Post reply

converting numeric fields to datetime

  • August 29, 2006 at 2:09 pm

    #173794

    I have 2 situations where I want to convert a numeric field(s) to a datatime datatype:

    (1)  the numeric field is 7,0, and is in "CYYMMDD" format.  For example, 1060829 means 08/29/2006, and 0941225 means 12/25/1994.  This field can contain a 0. 

    (2) there are 3 separate 2,0 fields, containing MM, DD and YY values.   For example, MM=08, DD=29, and YY=06.

    Thanks

     

     

     

     

  • August 29, 2006 at 2:38 pm

    #657395

    >>This field can contain a 0. 

    What does a zero mean in situation 1 ? NULL date ? Some other constnt past or future-dated date ?

    Declare @NumDate1 as numeric(7,0),

            @NumDate2 as numeric(7,0)

    Select @NumDate1 = 1060829, @NumDate2 = 0960829

    Select cast( cast( @NumDate1 + 19000000 as char(8)) As smalldatetime ),

           cast( cast( @NumDate2 + 19000000 as char(8)) As smalldatetime )

     

    Situation 2, what about years before 2000 ? What is in YY ?

  • August 29, 2006 at 2:51 pm

    #657397

    (1) yes - 0 means null

    (2) if YY < 40 then assume 20YY, else 19YY

    Thanks

  • August 30, 2006 at 1:12 pm

    #657669

    There are many ways to accomplish that. Here's one way:

    -- First case

    SELECT numdate

    , CASE numdate

    WHEN 0 THEN NULL

    ELSE CONVERT(datetime, CONVERT(varchar(8), ((1900 + ((numField / 1000000) * 100)) * 10000) + (numField % 1000000)))

    END

    FROM ...

    -- Second case

    SELECT yy, mm, dd

    , CONVERT(datetime, CONVERT(varchar(4), yy + CASE WHEN yy > 40 THEN 1900 ELSE 2000 END) + '-' + CONVERT(varchar(4), mm) + '-' + CONVERT(varchar(4), dd))

  • August 30, 2006 at 1:27 pm

    #657671

    That's what I needed.    Thanks!

     

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply