2

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?

6
  • 1
    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'.
    – Dan Guzman
    Mar 24 at 13:29
  • @DanGuzman Thank you. I am not very familiar with SQL Server. Will this work with hours and minutes as well? Mar 24 at 14:04
  • 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-01T23:59:59.999' is rounded to '2021-01-02T00:00:00.000').
    – Dan Guzman
    Mar 24 at 16:04
  • 1
    I suggest you read Tibor's discussion of the datetime datatype.
    – SMor
    Mar 24 at 19:23
  • @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? Apr 26 at 11:17
7

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

2
  • XKCD has a rant about this: xkcd.com/1179 Mar 25 at 1:12
  • 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. Mar 25 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.