Date conversion and culture: Difference between DATE and DATETIME

by

The ISO-8601 for DATETIME (the older type) is somehow “broken” or “adapted” (depending on whether you look at it as a bug or a feature) – you need to use YYYYMMDD (without any dashes) to make it work irrespective of the language settings.

For DATE or the DATETIME2(n) datatypes, this has been fixed and the “proper” ISO-8601 format YYYY-MM-DD will always be interpreted correctly.

-- OK because of "adapted" ISO-8601
SET LANGUAGE GERMAN;
DECLARE @dt DATETIME='20170113'; 

SELECT @dt;

SELECT CAST('20170113' AS DATETIME);
SELECT CONVERT(DATETIME, '20170113'); 

-- OK because of DATETIME2(n)
SET LANGUAGE GERMAN;
DECLARE @dt2 DATETIME2(0) = '2017-01-13'; 

SELECT @dt2;

SELECT CAST('2017-01-13' AS DATETIME2(0));
SELECT CONVERT(DATETIME2(0), '2017-01-13');

It’s a quirk of the DATETIME type (and not the only one….) – just register it, know about it – and move on (meaning: don’t use DATETIME anymore – use DATE or DATETIME2(n) instead – much nicer to work with!) 🙂

More Related Contents:

  1. How to return only the Date from a SQL Server DateTime datatype
  2. Best approach to remove time part of datetime in SQL Server
  3. Sql Server string to date conversion
  4. Get first day of week in SQL Server
  5. What is the significance of 1/1/1753 in SQL Server?
  6. How to get Time from DateTime format in SQL?
  7. How do I exclude Weekend days in a SQL Server query?
  8. Get dates from a week number in T-SQL
  9. SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  10. SQL Query to find the last day of the month
  11. How to determine the field value which can not convert to (decimal, float,int) in SQL Server
  12. How can I get the sum of multiple datetime values?
  13. Comparing dates stored as varchar
  14. T-sql – determine if value is integer
  15. How to calculate difference in hours (decimal) between two dates in SQL Server?
  16. Convert text to system date format [duplicate]
  17. How do I create a unique constraint that also allows nulls?
  18. How to convert SQL Server’s timestamp column to datetime format
  19. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  20. Insert Update trigger how to determine if insert or update
  21. SQL Server: Difference between PARTITION BY and GROUP BY
  22. How do you truncate all tables in a database using TSQL?
  23. SQL Server: How to select all days in a date range even if no data exists for some days
  24. Best way to store time (hh:mm) in a database
  25. SQL Server Group by Count of DateTime Per Hour?
  26. How do I select last 5 rows in a table without sorting?
  27. SELECT INTO a table variable in T-SQL
  28. How can I insert binary file data into a binary SQL field using a simple insert statement?
  29. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  30. What are row, page and table locks? And when they are acquired?

Leave a Comment