DATEDIFF working on yyyymmdd format SQL server

Question

Why the DATEDIFF accepts yyyymmdd format ? Isnt it only for yyyy-mm-dd format ?

The function DATEDIFF(datepart, start_date, end_date) is working fine when the dates are in yyyymmdd format e.g.

DATEDIFF(DAY, 20161201, 20161231);

It gives answer 30

and if you do DATEDIFF(DAY, 20161201, 20161231) + 1 -- end date inclusive

It gives 31

Thanks,

Aiden

Answer 1

This can be a bit hard to tease out, but it is explained in the documentation, in the section "Supported String Literal Formats for date".

There are five sections on this (but the last two are API specific). The first section is affected by internationalization settings and has a bunch of different formats.

The second is the ISO 8601 format. I think the hyphenated format conflicts with the very last format in the first table ("yyyy dd mm" without the hyphens).

The final section has this informative line "A six- or eight-digit string is always interpreted as ymd". Hence, 'YYYYMMDD' is always interpreted correctly. Hence, this is the unambiguous format for date/time.

Note: Because the functions you are using expect dates, if the integer works, then it is converted to a date using its string representation. Normally, date constants should be in single quotes.