Posts
259
Comments
464
Trackbacks
17
July 2010 Entries
Developers Are Still Migrating “Old School” Legacy Apps to SQL Server 2008

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

 

SQLFunctions

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. :-|

Posted On Monday, July 19, 2010 6:48 PM | Comments (0)