Post reply

Need help converting chars to a date result

  • January 12, 2004 at 2:08 pm

    #108648

    I have 2 columns in a SQL2000 db; one is one char and contains 3 or 4 – this is the year, 2003 or 2004 respectfully. The second column has 3 chars like 012 or 029 or 057 etc. – this represents the day of year, 012 is January 12, 029 is January 29, 057 is February 26 etc. How do I combine these two fields to get a date output in the result set? Ex. I need 4 & 029 to return 1/29/2004

  • January 12, 2004 at 2:25 pm

    #489645

    Not sure if this will really help, but what about

    declare @myyear int

    declare @mydays int

    declare @startofyear datetime

    set @myyear = 3

    set @mydays = 57

    set @startofyear =dateadd(d,@mydays-1,dateadd(yy,@myyear,cast('01.01.2000' as datetime)))

    select @startofyear

                                                          

    ------------------------------------------------------

    2003-02-26 00:00:00.000

    (1 row(s) affected)

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 12, 2004 at 2:34 pm

    #489648

    Try this...

    SELECT YourMonth = datepart(mm,CONVERT(datetime,convert(int,'057')))

        , YourDay = datepart(dd,CONVERT(datetime,convert(int,'057')))

        , YourDate =  convert(varchar, convert(varchar, datepart(mm,CONVERT(datetime,convert(int,'057')) ) ) + '/'

                    + convert(varchar, datepart(dd,CONVERT(datetime,convert(int,'057')) )) + '/'

                    + '20' + '04')




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • January 12, 2004 at 3:27 pm

    #489656

    Revising Frank's solution for the OP's data types:

    declare @myyear char,  @mydays char(3)

    set @myyear = '3'

    set @mydays = '057'

    select dateadd(d,@mydays-1,'200' + @myyear)



    --Jonathan

  • January 12, 2004 at 11:22 pm

    #489674

     

    @mydays-1 works though @mydays is char type.  

  • January 13, 2004 at 1:00 am

    #489681

    Hm, obviously I missed the char description. Gladly char with leading 0s will turn without problems into int.

    Jonathan, nice to see you started correcting wrong answers again!!!

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • January 13, 2004 at 6:13 am

    #489714

    Thank you all!!!

Viewing 7 posts - 1 through 6 (of 6 total)

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