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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Use datetime strings in ISO 8601 format:

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply