CODEWITHSUNDEEP
sqlsql-server
andrewm
andrewm

Reputation: 2642

SQL Server date formatting from string

We've recently migrated our database to a different server and since this I think the date format querying has changed somehow.

Previously we could use the following..

SELECT * FROM table WHERE date > 'YYYY-MM-DD'

However now we have to use..

SELECT * FROM table WHERE date > 'YYYY-DD-MM'

Can someone tell me what I need to change to get back to the previous version?

Upvotes: 5

Views: 2343

Answers (4)

Transact Charlie
Transact Charlie

Reputation: 2203

is you use different formats for the string then you can avoid this behaviour.

There are 2 iso formats that are always specific -- sql server will always parse them in the same way regardless of the server date format setting.

These are:

1) Short form : YYYYMMDD. Example '20120301' -- 1st March 2012

2) Long Form : YYYY-MM-DDTHH:MM:SS.msms'. Example '2012-03-01T12:13:00.000Z' -- 1st March 2012 at 13 minutes past 12 (PM)

In the long form the miliseconds is optional -- this is a perfectly acceptable ISO datetime '2012-03-01T12:13:00Z'

The Z at the end is time zone information. SQL Server doesn't actually require this. (though other products are a bit more exacting)

Try this for example:

DECLARE @foo DATETIME 

SET DATEFORMAT DMY

-- this will be the 3rd of january in DMY
SET @foo = '2012-03-01'
SELECT 'DMY: Not ISO', @foo

SET @foo = '20120301'
SELECT 'DMY: ISO', @foo

SET DATEFORMAT MDY

-- this will be the 1st of March in MDY
SET @foo = '2012-03-01'
SELECT 'MDY: not ISO', @foo

SET @foo = '20120301'
SELECT 'MDY: ISO', @foo

When you use text to enter dates you should always try to use one of the two ISO standards. It just makes things much more deterministic.

Short format (SQL Server) http://msdn.microsoft.com/en-US/library/ms187085(v=sql.90).aspx

ISO 8601 Format (SQL Server) http://msdn.microsoft.com/en-us/library/ms190977(v=sql.90).aspx

Upvotes: 2

Christian Hayter
Christian Hayter

Reputation: 31071

You are right, the date format is different between the servers.

Lots of people fall into the trap of assuming that if you specify a date literal as 'YYYY-MM-DD', it will be interpreted as that regardless of the current date format. This is incorrect. SQL Server sees the 4 digits at the start of the string and correctly deduces that they represent the year. However, it then uses the current date format to tell which way round the month and day are. If you are in the UK, for example, this puts you in an awkward situation because you need a date format of DMY to interpret a date literal like 'DD-MM-YYYY', but a date format of MDY to interpret a date literal like 'YYYY-MM-DD'.

You have several options:

  • SET DATEFORMAT YMD, and don't let users enter dates any other way.
  • Use the ODBC date literal syntax {d'YYYY-MM-DD'}. This will be parsed correctly regardless of the current date format. CONVERT(DATE, 'YYYY-MM-DD', 120) has the same effect.
  • Remove all literal values from your queries and use parameters instead. This is by far the best alternative, and I strongly recommend it.

Upvotes: 2

Devart
Devart

Reputation: 121922

Try this one -

Query:

SET DATEFORMAT ymd

Read current settings:

DBCC USEROPTIONS

Output:

Set Option                 Value
-------------------------- -----------------
...
language                   us_english
dateformat                 ymd
...

Upvotes: 2

Serge
Serge

Reputation: 6692

It's a matter of language/culture

Set Language 'us_english'

Upvotes: 0

Related Questions