"dateformat" independent form of datetime string in query for SQL Server?

Question

I am unfamiliar with SQL Server, and would like to do some ad-hoc searches in a dockerized legacy SQL Server database using SQL Server Management Studio where one of the fields is a datetime and I would like to use the BETWEEN operator to extract for a given period.

So something like

/****** Script for SelectTopNRows command from SSMS ******/
select card, sum(amount) as sum, count(*) as count
 FROM ... where cardused between '01-03-2021' and '03-03-2021' group by card

Now my problem is that this is interpreted as MDY dates and not DMY dates (which is what we use here). Server is TZ'ed to Europe/Copenhagen. My Windows 10 speaks Danish. If I run set dateformat dmy; first it is interpreted as expected.

Is there a documented format that always interprets correctly when used in a query string regardless of server settings?

Based on earlier work with Excel this might be something like

'2021-03-01 15:36'

to get this in my local timezone. Server and client will both run in the same timezone eventually - would this be an issue?

• Dan Guzman: In addition to an ISO 8601 string format, note that a BETWEEN will not provide the desired results when the datetime value includes a time other than midnight. Assuming you want all the values within the specified date range in your question, I suggest an inclusive start and exclusive end range, like cardused >= '20210301' AND cardused < '20210304'.
• Thorbjørn Ravn Andersen: @DanGuzman Thank you. I am not very familiar with SQL Server. Will this work with hours and minutes as well?
• Dan Guzman: Yes, the inclusive start/exclusive end technique works with a time component too. Be aware that the legacy datetime type (not datetime2) is only accurate to 1/300 fractional seconds so some fractional second values will get rounded (e.g. '2021年01月01日T23:59:59.999' is rounded to '2021年01月02日T00:00:00.000').
• SMor: I suggest you read Tibor's discussion of the datetime datatype.
• Thorbjørn Ravn Andersen: @DanGuzman Just to clarify: The thing you are pointing out with BETWEEN is that if it is used with date-only values then it does not include events on that day but after midnight. If the values include a time-of-day as well, then that is not a concern?

Answer 1

Use datetime strings in ISO 8601 format:

YYYY-MM-DDThh:mm:ss[.mmm]
YYYYMMDD[ hh:mm:ss[.mmm]]

To use the ISO 8601 format, you must specify each element in the format, including the T, the colons (:), and the period (.) that are shown in the format.

The brackets indicate that the fraction of second component is optional. The time component is specified in the 24-hour format.

The T indicates the start of the time part of the datetime value.

The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format isn't affected by the SET DATEFORMAT or SET LANGUAGE setting.

More info

• Concrete Gannet: XKCD has a rant about this: xkcd.com/1179
• Tibor Karaszi: I didn't know that MS nowadays refers to the "unseparated" also as "ISO 8601" format. You learn something every day. A tiny addition: The format with separators space instead of without that T (as in YYYY-MM-DD [hh:mm:ss[mmm]] is also language neutral, but only when converted to the new datetime types. If you even suspect you'll deal with the old types, you'll end up with those conversions being language dependent.