SQL Server - Date Conversion

Question

I have an issue converting dates in SQL Server 2008

The value I am converting looks like this: 30-NOV-44 08.00.00.000000000

The result I get back looks like this: 2044-11-30 00:00:00.000

The code I am using is as follows:

SELECT TOP 1000 [USER_ID]
      ,CONVERT(datetime,SUBSTRING([DATEOFBIRTH],0,11),112) AS ConvertedDate
      ,[DATEOFBIRTH]
  FROM [IAM_Perf].[dbo].[01_Data]
  WHERE DATEOFBIRTH IS NOT NULL

Whatever parameter I use on the CONVERT function I get the same format back and a lot of the dates are being converted into the future??!

The results do change when I switch between date and datetime in the first argument of the CONVERT but it makes no impact on the incorrect results.

I am using the substring function because otherwise it will not convert the value and throws this error:

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

I'm not sure how to combat this, I cannot guaruntee the format will always remain the same either else I would just manually reformat it using some T-SQL

Any one have any Ideas?

Thanks,

Matt

Answer 1

2010/10/10_

Convert and then use subtring select *,substring((convert(varchar(12),hire_date,121)),1,10) from employee

or

This may carry the space next to the date SUBSTRING([DATEOFBIRTH],0,11),112)

or Try this same with 10 digit, conversion may happen good SUBSTRING([DATEOFBIRTH],0,10),112)

Answer 2

You can configure the cutoff year using

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'two digit year cutoff', 2011
RECONFIGURE

Note that this is a server-wide setting and can break the queries on other databases.

Answer 3

Since future dates are perfectly valid in SQL Server, the system has to use a heuristic when you only pass it a 2 digit date. I believe the default heuristic is that years 0-49 are in the 21st century, and years 50-99 are in the 20th century. This is, however, configurable.

You need to fix this data before it ends up in your database, and should preferably be storing DateOfBirth in a datetime column anyway (rather than as a string).

---

A datetime value doesn't have a format - but if you've converted something into a datetime, and then attempt to display it, it will always be converted back into a string in the same format (unless you explicitly convert it using CONVERT).