Post reply

Date Query Help

  • January 24, 2012 at 1:22 pm

    #96695

    Hi,

    I have time info that is being imported into a table. The times are 720 (7:20 AM) 330 (3:30 PM) etc...is there way to convert these to a datetime. such as 1/24/2012 7:20... The format of the date doesn't matter much, and the date it is converting to can just be the current date, but I need the time that is being imported. I have tried some convert functions, but I can only seem to get it treat the 720, etc like 720 seconds...

    Thanks

  • January 24, 2012 at 1:30 pm

    #1437932

    What differentiates 720 as AM vs PM?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • January 24, 2012 at 1:34 pm

    #1437934

    Nothing on the import...It doesn't matter if its marked AM or PM after the convert, just as long as those times are in there.

  • January 24, 2012 at 1:36 pm

    #1437937

    DECLARE @times TABLE

    (timeValue VARCHAR(5))

    INSERT @times (timeValue)

    SELECT '745'

    UNION ALL SELECT '1115'

    UNION ALL SELECT '330'

    SELECT CONVERT(DATETIME, '1/1/1900 ' + STUFF(RIGHT('0000' + timeValue,4),3,0,':'))

    FROM @times

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • January 24, 2012 at 1:40 pm

    #1437939

    nice, thank you!

  • January 24, 2012 at 1:57 pm

    #1437952

    One more question...anything from 1065-1095 I am getting error The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    All other values work like 1050, 1110, 720, 330 etc

  • January 24, 2012 at 2:00 pm

    #1437955

    So what time is 1065 ?

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • January 24, 2012 at 2:02 pm

    #1437957

    ahh, that makes sense, not sure how I didn't notice that...I am not sure what time that is suppose to be so I will just exclude those for now...thanks for your help

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

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