Search code examples
sqlsql-serverdatetimevarchariso8601

SQL- convert varchar yyyymmdd-ttttt to iso 8601 format

I am looking for a way to convert varchar yyyymmdd-ttttt to iso 8601 format. Here is an example of the value I am looking to convert: 

20120503-56320

I have already tried:

CONVERT(NVARCHAR(30), ia.alcreateStamp, 126)

which doesnt not change the format. Thank you in advance.

Beth

Solution

  • The first problem here is you are storing a datetime as an (n)varchar. Store dates, and times, as what they are, a date, time or datetime(2).

    When using CONVERT to change the datatype to a (n)varchar, providing a style code tells the data engine the format the string should be in from the corresponding datatype (in this case a date or datetime(2) datatype). You're converting an nvarchar to an nvarchar, so that style code is completely ignored.

    I'm guessing that the value 20120503-56320 is the date 2012-05-03 15:34:40 (there are 86400 seconds in a day, and I am assuming -56320 is the seconds into the day), thus to convert your value to a datetime2 you would use:

    SELECT DateString,
       DATEADD(SECOND,CONVERT(int,RIGHT(V.DateString,LEN(V.DateString) - CI.I)),CONVERT(datetime2(0),LEFT(V.DateString,CI.I -1))),
       CONVERT(varchar(19),DATEADD(SECOND,CONVERT(int,RIGHT(V.DateString,LEN(V.DateString) - CI.I)),CONVERT(datetime2(0),LEFT(V.DateString,CI.I -1))),126)
FROM (VALUES('20120503-56320')) V(DateString)
     CROSS APPLY (VALUES(CHARINDEX('-',V.DateString))) CI(I);

    Honestly, at this point, you should stop. Pass the datetime2 value to your presentation layer and handle the formatting there.

    If you really want to improve, however, then fix your datatype. One method might be to add a new persisted column:

    ALTER TABLE YourTable ADD DateValue AS DATEADD(SECOND,CONVERT(int,RIGHT(DateString,LEN(DateString) - CHARINDEX('-',DateString))),CONVERT(datetime2(0),LEFT(DateString,CHARINDEX('-',DateString) -1))) PERSISTED;