0

I'm trying to pin down a failing query and it is related to dates. I have managed to reproduce the issue in a fairly simple script (see below). In order to eliminate any potential language and region issues related to date formats we created a clean VM with Windows and SQL Server installed all using UK/British English options where availble.

My script is below:

set language 'british'
go
declare @datetimeYMD_T datetime = '2022-10-31T12:34:56.123' -- WORKS (expected)
declare @datetimeYMD   datetime --= '2022-10-31 12:34:56.123' -- FAILS (NOT expected) 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'
declare @dateYMD       date     = '2022-10-31'              -- WORKS (expected)
declare @datetimeYDM   datetime = '2022-31-10 12:34:56.123' -- WORKS (NOT expected)
declare @dateYDM       date    -- = '2022-31-10'              -- FAILS (expected) 'Conversion failed when converting date and/or time from character string.'
select @@LANGUAGE as Language, @datetimeYMD_T as 'YMD_T DateTime', @datetimeYMD as 'YMD DateTime', @dateYMD as 'YMD Date', @datetimeYDM as 'YDM DateTime', @dateYDM as 'YDM Date'

What I don't understand is why a YYYY-MM-DD format works for date datatype work but not a datetime datatype (without the T).

Also if I changed the script to have a us_english language then as I would expect the YMD formats work and the YDM fail.

I appreciate just adding a T would resolve things but that would involve updating lots of scripts, etc. and it's something I'd rather avoid. I'm also concerned this would be masking a deeper issue that could be manifesting itself in other, as yet unnoticed ways and/or could be a ticking time bomb.

This is being run on Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) on a Windows Server 2019 OS.

B_D
  • 225
  • 2
  • 16
  • 1
    There are only a [few formats](https://dba.stackexchange.com/questions/166771/what-date-time-literal-formats-are-language-and-dateformat-safe) that are safe in all circumstances; `yyyy-MM-dd HH:mm:ss.fff` is *not* one of them, confusing and unwanted as this may be. – Jeroen Mostert May 10 '23 at 10:51
  • 1
    For the old date and time dataypes `(small)datetime`) `yyyy-MM-dd( hh:mm:ss(.nnnnnnn))` is *not* unambiguous. For those of use that aren't American the `varchar` value `'2022-31-10 12:34:56.123'` would read as the 10th day of the 31st month. Only `yyyyMMdd` and `yyyy-MM-ddThh:mm:ss(.nnnnnn)` are unambiguous, regardless of language/`DATEFORMAT` setting and data type. – Thom A May 10 '23 at 10:52
  • If you want a "safe" or more accurately "predictable" configuration, leaving the defaults at US English is by far your best bet, rather than making them all UK -- locale-specific formatting and parsing should be left to client-side code in any case, so that should not be a motivating factor. Even then ambiguous date/time values should be avoided, of course, but assuming that sloppily written software expects US formats is a safer bet than any other format. – Jeroen Mostert May 10 '23 at 10:58
  • The question itself is not a complete duplicate of the question I've linked to, but my answer there does explain why `datetime` fails when `date` does not, among other things that you should be aware of when using string representations of dates in SQL Server – Zohar Peled May 10 '23 at 12:14

0 Answers0