CODEWITHSUNDEEP
sqlsql-server
Aiden
Aiden

Reputation: 460

DATEDIFF working on yyyymmdd format SQL server

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

Upvotes: 3

Views: 6348

Answers (2)

fahad
fahad

Reputation: 164

Actually its the sql server default format of dates but you can format your dates according to your requirements by following the link: http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270503

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.

Upvotes: 1

Related Questions