Date Conversion help

Hi,

I have my dates in DB2 source in two formats -

Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1

Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25

I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.

If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.

Any help is appreciated.

Thanks,

Vivek1 indicates century? What century is it supposed to indicate? Are we talking the year 1903? I am assuming it is supposed to be 1=2000 in the code below:

CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

IF LEN(@.db2_date) = 7
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 1 AS VARCHAR(1)) + '0' + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||Yes 1 indicates century 2000 and 0 indicated 1900. So 10305 would be 2003. From first 3 digits. And 19505 would be 1995.|||You should be able to use the function inline in your queries:

SELECT dbo.fn_convert_db2_date(the_db2_date_field)

will return the converted SMALLDATETIME field|||Hi,

I am not able to convert 09525 using the function. Also the date I need is of the format - mm/dd/yyyy ex: 5/21/2003. When I execute the function for '10325', I get 2004-05-01 00:00:00. I do not need the time part. Also the format is different.

Thanks|||The date format is always store in the same fashion in SQL Server. What you want to do is alter the format when you display the date in your UI/report/whatever it is.

CONVERT(the_date_field, 101) will give you a VARCHAR of the date in mm/dd/yyyy.

'10325', according to your definition, would be Year=2003, Month = 25.

Reworking the year conversion. Sorry about the shoddy workmanship there.|||CREATE FUNCTION fn_convert_DB@._date (@.db2_date VARCHAR(7))

RETURNS SMALLDATETIME AS

DECLARE @.new_date AS SMALLDATETIME

IF LEN(@.db2_date) = 5 AND ISDATE(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(RIGHT(@.db2_date,2) + '/1/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS SMALLDATETIME)

IF LEN(@.db2_date) = 7 AND ISDATE(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2))=1
SELECT @.new_date = CAST(SUBSTRING(@.db2_date,4, 2) + '/' + RIGHT(@.db2_date, 2) + '/' + CAST(CAST(LEFT(@.db2_date, 1) AS INT) + 19 AS VARCHAR(2)) + SUBSTRING(@.db2_date, 2, 2) AS DATETIME)

RETURN @.new_date|||New function checks for proper date format before converting, and returns NULL if format is wrong. Oh, and it deals properly with centuries.