Post reply

How to convert date in Varchar to TimeZone format?

  • May 20, 2008 at 1:05 am

    #185806

    Hi,

    Iam having a date '19-05-2008 09:46:35 AM' (in Varchar format). How to convert this date to TimeZone (ISO8601 with time zone Z) format?

    For Example,

    My input date is '19-05-2008 09:46:35 AM' and I need output as '2008-05-19T09:46:35.00Z'.

    How to convert this?

  • May 20, 2008 at 1:18 am

    #817611

    I dont think you can using any of the built in functions.

    You could do this using a User Defined Function that returns a varchar string in the format you requested.

    Matt.

  • May 20, 2008 at 2:29 am

    #817638

    No.. I think a way is there to convert.

  • May 20, 2008 at 3:19 am

    #817654

    There are 2 solutions as listed below. The second solution using a "convert(convert)" is prefered as it does perform editing and does not rely on the date components having a fixed position, that is, a leading zeroes for components would be required. For the output, you have indicated a time precision to a hundredth of a second but the ISO standard is thousandths.

    declare @TS char(22)

    set@TS = '19-05-2008 09:46:35 AM'

    --19-05-2008 09:46:35 AM

    --1234567890123456789012

    selectsubstring(@TS,07,4) + '-' + -- year

    substring(@TS,04,2) + '-' + -- month

    substring(@TS,01,2) + 'T' + -- day

    substring(@TS,12,8) + '.00Z' -- time

    selectconvert( varchar(255) , ( convert(datetime,@ts,105 ) ) , 126 ) + '.00Z'

    [\code]

    SQL = Scarcely Qualifies as a Language

  • May 20, 2008 at 3:38 am

    #817664

    Nice example Carl, thanks.

    Matt.

  • May 20, 2008 at 4:28 am

    #817688

    It's working fine. Thank you very much.

  • May 20, 2008 at 5:24 pm

    #818286

    Or, you can go straight in...

    [font="Courier New"]DECLARE @SomeDate VARCHAR(20)

    SET @SomeDate = '19-05-2008 09:46:35 AM'

    SELECT CONVERT(VARCHAR(20),CONVERT(DATETIME,@SomeDate,105),126)+'.00Z'[/font]

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 20, 2008 at 7:54 pm

    #818321

    Satish, can you please elaborate why you want to store the date in this format?

  • May 20, 2008 at 10:40 pm

    #818351

    To match the date time with the XML DataTime ( that XML is stored in XMLType column) value.

    My input date is '19-05-2008 09:46:35 AM' and the date stored inside the XML (in XML Type) column is '2008-05-19T09:46:35.00Z'. I have to match these dates. When i compare directly, it gives false. So i have to conver the input date to TimeZone format.

  • May 21, 2008 at 7:32 pm

    #818951

    Thanks for the explanation... why not just import the XML into a table as "real" data and use DateTime columns? Again, just curious...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

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