I’ve just spent a fun hour wondering why two seemingly identical queries were returning different data. It turned out to be an issue with DATETIME in SQL Server.
SQL interprets a DATETIME of the format “yyyymmdd” differently to “yyyy/mm/dd” or “yyyy-mm-dd”. The former is “unseperated” while the latter is “seperated”, and the “seperated” format depends on the current language and date format of SQL Server. If you have the date format set to the British standard “dmy” (as I did), then the month and day portion of the input get swapped. This can cause an error of “The conversion of a varchar data type to a datetime data type resulted in an out-of-range value” if the last two digits are greater than 12, as SQL Server attempts to use the greater than 12 number for the month.
Alternatively if you have a number less than or equal to 12, you won’t get an error but you will return incorrect data. This is much more confusing and much more problematic.
For example, the following queries (on AdventureWorks) are identical, except for the format of the date in the query. The first is “unseperated”, the second is “seperated”:
Query 1 (unseperated):
SELECT [DueDate], [OrderQty], [UnitPrice]
FROM [Purchasing].[PurchaseOrderDetail]
WHERE DueDate > '20060410'
Query 2 (seperated):
SELECT [DueDate], [OrderQty], [UnitPrice]
FROM [Purchasing].[PurchaseOrderDetail]
WHERE DueDate > '2006-04-10'
If you run these with the dateformat set to ‘dmy’ (SET DATEFORMAT ‘dmy’) you’ll see that the second query returns data from the 4th January rather than the 1st April.
This affects DATETIME, but works correctly for DATE and DATETIME2. Converting the string to either DATE or DATETIME2 only brings in data after the 1st April, as expected:
DATE:
SELECT [DueDate], [OrderQty], [UnitPrice]
FROM [Purchasing].[PurchaseOrderDetail]
WHERE DueDate > CONVERT(DATE, '2006-04-01')
DATETIME2:
SELECT [DueDate], [OrderQty], [UnitPrice]
FROM [Purchasing].[PurchaseOrderDetail]
WHERE DueDate > CONVERT(DATETIME2, '2006-04-01')
However, SQL automatically converts a string into a DATETIME.
Microsoft recommend using DATE or DATETIME2 over DATETIME: https://technet.microsoft.com/en-us/library/ms187819.aspx
View Darren Hall's profile
RSS – Posts