unexpected results when converting nvarchar to datetime using substrings

Question

I have to deal with the following columns:

StartTime (nvarchar(10), null)
StartDate (nvarchar(10), null)

StartTime contains the Time as following: hhmmss

StartDate contains the Date as following: yyyymmdd (m=month)

To concatenate and convert StartDate and StartTime to a datetime i use the following code inside a stored procedure:

TRY_CAST(SUBSTRING(xy.StartDate,7,2) + '.' + SUBSTRING(xy.StartDate,5,2) + '.' + SUBSTRING(xy.StartDate,1,4) + ' ' + SUBSTRING(xy.StartTime,1,2) + ':' + SUBSTRING(xy.StartTime,3,2) + ':' + SUBSTRING(xy.StartTime,5,2) as datetime) as FULLSTARTDATE

Now imagine these values for two cases:

Case 1:

StartTime = 000000
StartDate = 20191201
FULLSTARTDATE = 2019-01-12 00:00:00.000

Case 2:

StartTime = 000000
StartDate = 20191220
FULLSTARTDATE = NULL

Why does Case 2 not output 2019-20-12? It may be a trivial issue but i can't seem to figure it out for almost an hour now.

score 2 · Answer 1 · answered Feb 19 '20 at 09:50

TSQL DATETIME ISO 8601

select 
TRY_CAST(SUBSTRING(xy.StartDate,1,4) + '-' + SUBSTRING(xy.StartDate,5,2) + '-' + SUBSTRING(xy.StartDate,7,2)+ 'T' + SUBSTRING(xy.StartTime,1,2) + ':' + SUBSTRING(xy.StartTime,3,2) + ':' + SUBSTRING(xy.StartTime,5,2)+'Z' as datetime) as FULLSTARTDATE,
try_cast(StartDate + ' ' + stuff(stuff(StartTime, 5, 0, ':'), 3, 0, ':') as datetime)
from
(values('20191201', '000000' ), ('20191220', '000000')) as xy(StartDate, StartTime);

score 1 · Accepted Answer · answered Feb 19 '20 at 10:03

It seems to me, that you are composing a date/time string in the form "DD.MM.YYYY HH:MM:SS", which seems to get interpreted as "MM.DD.YYYY HH:MM:SS" instead.

I see two possible solutions (but there might be more):

1: use "YYYY.MM.DD HH:MM:SS", which cannot be interpreted "incorrectly":

TRY_CAST(
    SUBSTRING(xy.StartDate,1,4) + '.' +
    SUBSTRING(xy.StartDate,5,2) + '.' +
    SUBSTRING(xy.StartDate,7,2) + ' ' +
    SUBSTRING(xy.StartTime,1,2) + ':' +
    SUBSTRING(xy.StartTime,3,2) + ':' +
    SUBSTRING(xy.StartTime,5,2) as datetime) as FULLSTARTDATE

2: use CONVERT instead of TRY_CAST:

CONVERT(DATETIME, xy.StartDate, 104) +
CONVERT(DATETIME,
  SUBSTRING(xy.StartTime,1,2) + ':' +
  SUBSTRING(xy.StartTime,3,2) + ':' +
  SUBSTRING(xy.StartTime,5,2), 108) as FULLSTARTDATE

unexpected results when converting nvarchar to datetime using substrings

2 Answers2