Convert sting to datetime

How to convert in MS SQL server 2005 from string to Datetime datatype

SELECT
[customer_key] ,
[company_code],
[distributor_code] ,
[dist_center],
[cust_code_ref] ,
[customer_name],
[customer_code],
[status],
[changedby] ,
CASE WHEN [status_date] ='0001-01-01' THEN CONVERT(DATETIME,'0001-01-01',111) ELSE CONVERT(DATETIME,[status_date],111) END AS [status_date],
[status_time]
FROM [stagedw].[dbo].[customer_cross_reference_src] a

Giving error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

July 29th, 2015 3:29am

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


It's because the type "datetime" supports only years in the range 1753 - 9999, but not 0001; here you have to use datetime2 or date instead
Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 3:38am

But I am using MS SQL Server 2005 which does not have Datetime2 or date.

Is there any other way ?

Thanks for ur comment.

July 29th, 2015 3:44am

Can you change/replace 0001 to 1900 in CASE expression (THEN)?
Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 3:49am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics