Post reply

convert date time

  • November 6, 2013 at 5:28 am

    #149992

    this is my query

    select 1 as CID, substring(abd,24,5) as EID,substring(abd,3,12) as Checktime,substring(abd,17,2) as [status],substring(abd,32,2) as BID from ABCD

    its give me this data

    cid-----eid--------checktime----status-bid

    1------25850----201310311133---01----2

    1------17018----201310311135---01----2

    1------25850----201310311137---01----2

    i want this type of data

    cid-----eid--------checktime-------status---bid

    1------25850----2013-10-31 11:33---01-----2

    1------17018----2013-10-31 1135----01-----2

    1------25850----2013-10-31 1137----01-----2

    thanks for the help

    immad

  • November 6, 2013 at 5:44 am

    #1664421

    That's why the CONVERT function exists.

    You might want to format the datetime a little bit differently.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 6, 2013 at 8:20 am

    #1664483

    CONVERT won't help with the string as is. You need to format it a little bit.

    WITH SampleData( cid,eid,checktime,status,bid) AS(

    SELECT 1,'25850','201310311133','01','2' UNION ALL SELECT

    1,'17018','201310311135','01','2' UNION ALL SELECT

    1,'25850','201310311137','01','2')

    SELECT cid,

    eid,

    CONVERT( datetime, STUFF( STUFF(checktime, 11, 0, ':'), 9, 0, ' ')),

    status,

    bid

    FROM SampleData

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 6, 2013 at 8:39 am

    #1664488

    Luis' code will work if there are always the correct number of digits stored. So September is 09, never just 9. If you don't always get the same number of digits, you'll need to check for that.

  • November 7, 2013 at 3:57 pm

    #1664952

    I wrote an article on custom date formats[/url] last year. Maybe some of the functions there can help.

    -- Gianluca Sartori

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

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