Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: 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,

    Vivek
    Last edited by vivek_vdc; 01-28-04 at 10:53.

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    1 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
    -bpd

  3. #3
    Join Date
    Sep 2003
    Posts
    176
    Yes 1 indicates century 2000 and 0 indicated 1900. So 10305 would be 2003. From first 3 digits. And 19505 would be 1995.

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    -bpd

  5. #5
    Join Date
    Sep 2003
    Posts
    176
    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

  6. #6
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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.
    -bpd

  7. #7
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    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
    -bpd

  8. #8
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    New function checks for proper date format before converting, and returns NULL if format is wrong. Oh, and it deals properly with centuries.
    -bpd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •