Post reply

Need to convert datetime to W3C Date and Time format

  • December 21, 2014 at 4:57 pm

    #293245

    I have a standard datetime and I need to convert it to the client specification of:

    YYYY-MM-DDThh:mm:ssTZD

    eg: 2009-04-16T19:20:30+08:00

    I am not sure of the easiest way to do this.

    The test code below gets me part of the way but I am unsure on how to get the offset on the end without hardcoding to much.

    DECLARE @datetime DATETIME = '2014-12-20 12:30:00'

    SELECT CONVERT(VARCHAR(30),@datetime,127)

    thanks

  • December 21, 2014 at 11:15 pm

    #1765904

    Perhaps this?

    SELECT CONVERT(VARCHAR(100), CAST('2014-01-01' AS DATETIMEOFFSET), 126)

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • December 22, 2014 at 4:38 am

    #1765945

    To be able to add real offset value you need some additional source of data.

    declare @off char(6) ='+04:30';

    declare @src datetime = getdate(); -- source value

    select convert(varchar(50),convert(datetimeoffset(0),convert(varchar(50), @src) + @off),126) as dtoc

Viewing 3 posts - 1 through 2 (of 2 total)

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