CODEWITHSUNDEEP
sqlsql-serverdatetimetype-conversiondatetime2
Programmer
Programmer

Reputation: 479

Why can datetime allow this decimal format but datetime2 won't?

I just ran into an issue where my program was not able to convert a string to a datetime2 value. The value was 12/17.2020. When converting this to datetime it worked, but when converting it to datetime2 it didn't. What is the reason for this? How can 12/17.2020 be valid?

Upvotes: 1

Views: 292

Answers (1)

anon
anon

Reputation:

12/17.2020 is a terrible format to begin with, not just because of mixed punctuation but also because it is ambiguous. Luckily we can tell that is mm/dd.yyyy but what if you showed us 12/08.2020?

How can 12/17.2020 be valid?

Just because it is valid in one context doesn't mean you should use it (or be mad at the newer types that "break" it). It is not valid for any good reason - this format certainly isn't listed in the Supported Literal String Formats section of the datetime docs.

It is valid simply because the older data types (datetime/smalldatetime) were more forgiving. They also let you use shorthand like this:

DECLARE @d datetime;
SET @d = 0;
SELECT @d + 1;

Try those with date or datetime2 and you will be met with:

Msg 206, Level 16, State 2
Operand type clash: int is incompatible with datetime2

The why isn't really important. What you need is a workaround.

  1. Ideally, you can fix your inbound format to be an unambiguous format universally understood by all date/time data types, and immune to problematic factors like regional settings, language settings, and dateformat settings:

    yyyymmdd

  2. You can get around this in some cases using the horribly inefficient FORMAT function or replacing first but, if you can't change the incoming format, probably your simplest answer is to just run two converts:

    SELECT CONVERT(datetime2, CONVERT(datetime, '12/17.2020'));

  3. If you're just trying to blindly pass 12/17.2020 into a stored procedure parameter or insert it directly into a column from your application, you're going to be out of luck. Please see 1.

Upvotes: 3

Related Questions