I recently received an email from a self-described “old school” developer who had attended one of our Visual FoxPro classes from a number of years back (2001?). He had some questions about working with SQL Server 2008 and T-SQL. He asked about a number of built in VFP/xBase functions that don’t have a counterpart in T-SQL, specifically some that deal with dates and strings. I too went through that migration waaaaay back when and have since created some UDFs that reproduce some familiar and very useful “old school” VFP/xBase functions in T-SQL. I thought I’d share a few here in the hopes that they help other “old school” (and maybe some “new school”) developers making the leap to SQL Server.
DTOC() converts a passed in Datetime value into a mm/dd/yyyy formated date string.
CREATE FUNCTION [dbo].[DTOC] (@DateTimeIn datetime)
RETURNS varchar(max)
AS
BEGIN
RETURN(
REPLACE(STR(DATEPART(month, @DateTimeIn),2),' ','0')+'/'+
REPLACE(STR(DATEPART(day, @DateTimeIn),2),' ','0')+'/'+
REPLACE(STR(DATEPART(year, @DateTimeIn),4),' ','0')
)
END
DTOS() converts a passed in Datetime value into a yyyymmdd formated date string.
CREATE FUNCTION [dbo].[DTOS] (@DateTimeIn datetime)
RETURNS varchar(max)
AS
BEGIN
RETURN(
REPLACE(STR(DATEPART(year, @DateTimeIn),4),' ','0')+
REPLACE(STR(DATEPART(month, @DateTimeIn),2),' ','0')+
REPLACE(STR(DATEPART(day, @DateTimeIn),2),' ','0')
)
END
One thing to watch out for is using a DTOC() column in the order by clause. The sort will occur left to right which means 03/14/2009 will appear before 10/22/2007 and that may not be the results you want. Ordering by a DTOS() column will result in the proper sort order since its format is yyyymmdd.
PADL() pads a passed in string with characters to a specified length on the left. This one is very handy for formatting information like order numbers, invoice numbers and the like.
CREATE FUNCTION [dbo].[PADL] (@StringIn varchar(max), @StringLength int, @PadWith char(1))
RETURNS varchar(max)
AS
BEGIN
RETURN (REPLICATE(@PadWith,(@StringLength - Len(@StringIn))) + @StringIn)
END
PADR() pads a passed in string with characters to a specified length on the right.
CREATE FUNCTION [dbo].[PADR] (@StringIn varchar(max), @StringLength int, @PadWith char(1))
RETURNS varchar(max)
AS
BEGIN
RETURN (@StringIn+REPLICATE(@PadWith,(@StringLength-Len(@StringIn))))
END
ALLTRIM() returns a string with leading and trailing spaces removed. Using variable length character fields reduces the need for this one quite a bit but you never know when you’re going to want to trim all the spaces off the left and right sides of a string.
CREATE FUNCTION [dbo].[ALLTRIM](@StringIn varchar(max))
RETURNS varchar(max)
BEGIN
RETURN LTRIM(RTRIM(@StringIn))
END
Here is a sample select statement using all of the “old school” functions.
select
dbo.DTOC(InvoiceDate) as DTOCDate,
dbo.DTOS(InvoiceDate) as DTOSDate,
dbo.PADL(InvoiceNumber,10,'0') AS PADLInvoice,
dbo.PADR(InvoiceNumber,10,'0') AS PADRInvoice,
dbo.ALLTRIM(LastName+', '+FirstName) AS ALLTRIMLastName
from SampleData order by DTOSDate
In addition to being comfortable with these function from years gone by another benefit to implementing these (and others) is reduced code migration time. Legacy SQL SELECT statements using these native language functions can be copied in to a SQL Server stored procedure and after a pasting a few dbo.’s in front of the functions, those queries will be running in no time.
While these may seem simple to an experienced T-SQL developer, how many of you can still remember way-back-when to the days of being a SQL Server newbie?
Have a day. :-|