converting to datetime data type

  • year=2005




    I have these values with me and I wanted to convert this to a datetime field.

    SELECT CAST(year AS varchar)+'-'+CAST(month AS varchar)+'-'+CAST(day AS varchar)+' '+CAST(time as varchar)+':'+'00'

     FROM tab

    I am trying to convert this into datetime by using the above quesry.

    But I wanted to convert month 5 as as 05 and time 9:30 as 09:30.

    And also If i get day as 2 I need to convert it to 02.

    Any help would be greatly appreciated.


  • select CONVERT ( datetime,getdate(),102 ) might do what you want:

    2005-05-27 11:55:35.467


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This may not achieve what you want, but I am guessing this is for display reasons. 

    SELECT CONVERT( varchar, CONVERT( datetime, '1/1/2005 09:00:00 AM'), 101) + ' ' +  CONVERT( varchar, CONVERT( datetime, '1/1/2005 09:00:00 AM'), 108)

    I wasn't born stupid - I had to study.

  • I tried that too Lowell, but my setting in Query Analyzer yielded:  5/27/2005 12:18:47 PM. 

    That is why I suggested a more, (shall we say) convoluted answer. 

    I wasn't born stupid - I had to study.

  • This should give you a hint:

    select Right('00' + cast(month(getdate()) as varchar),2)



  • Farrell... there is a 2 steps solution... i think this will work fine... at least it worked with my configuration...

    But I suggest that you take a look at BOL, under topic CAST & CONVERT... there is a table comparing the diferent kinds of convertions that you can perform using cast and covert functions.

    declare @date datetime


         @Date = convert(datetime, (year+'-'+month+'-'+day+' '+time+':00'), 102)

    FROM YourTable

    SELECT convert(varchar(20),@date,110)+' '+convert(varchar(20),@date,108)


    Nicolas Donadio

    Sr. Software Developer

    DPS Automation






  • Thanks Nicolas.  I understand those.  I misread the initial question and thought this was a date and not seperate fields for each part of a date.  That is why I suggested the concatenation using a specific date so the leading zero's would display.  ooops... 

    I wasn't born stupid - I had to study.

  • Another approach might be:




        (@year * 10000 +

        @month * 100 +


       AS CHAR(8))+' '+ @time



    Note, that this doesn't check for a valid date before trying to CAST to DATETIME. If you also need to do this, see if this helps:

    Frank Kalis
    Microsoft SQL Server MVP
    My blog:[/url]

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

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