Convert a datetime to a legacy iSeries/DB2 format.

Last week we converted an unusual datetime format from a DB2 database into SQL Server's smalldatetime. This week we'll go back from a smalldatetime to the decimal number stored as mmddyy that is being used by the RPG application on the iSeries that uses the DB2 database. Translating back to the iSeries's format is necessary so that updates can be made from the SQL Server database to the linked DB2 server.

This week's function is udf_DT_ToDecimal, which is the complement of last week's udf_DT_FromDecimal. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  FUNCTION dbo.udf_DT_ToDecimal (

    @Date datetime -- SQL datetime to convert

) RETURNS numeric (6,0) -- date stored in the AS/400 format
/* 
* Converts from a SQL datetime to the numeric version as stored 
* on the iSeries.  In this case the iSeries has a date stored as
* a numeric in the form mmddyy. 
* 
* Example:
SELECT dbo.udf_DT_ToDecimal ('2001-01-01') [Jan 1, 2001]
     , dbo.udf_DT_ToDecimal ('1999-12-31') [Dec 31, 1999]
     , dbo.udf_DT_ToDecimal ('2002-2-28') [Feb 28, 2002]
*
* © Copyright 2004 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the T-SQL UDF of the Week Newsletter Vol 2 #23
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
******************************************************************/
AS BEGIN
	RETURN       (Month(@Date) * 10000)
               + (Day(@Date) * 100)
               + (YEAR(@Date) - CASE WHEN YEAR(@Date) > 1999 
                                     THEN 2000 
                                     ELSE 1900 END)
END
GO

GRANT EXEC on dbo.udf_DT_ToDecimal TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's test out the function:

SELECT dbo.udf_DT_ToDecimal ('2001-01-01') [Jan 1, 2001]
     , dbo.udf_DT_ToDecimal ('1999-12-31') [Dec 31, 1999]
     , dbo.udf_DT_ToDecimal ('2002-2-28') [Feb 28, 2002]
GO

(Results)
Jan 1, 2001 Dec 31, 1999 Feb 28, 2002 
----------- ------------ ------------ 
      10101       123199        22802 

With udf_DT_FromDecimal and udf_DT_ToDecimal available it is possible to keep the two database synchronized and handle updates from either side of the link.

Next week we'll handle another similar date format that I ran into on the iSeries.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick