How to convert a date field to a date time format? SQL 2008

Question

The conversion of a date data type to a datetime data type resulted in an

out-of-range value.

The statement has been terminated.

I just need to convert a date field to a date time format.

@ Rahul: Its really a long query, but now I discovered where is the error. see my question — Dragon, Apr 27 '15 at 10:53
Are you sure the destination type is datetime and not timestamp? — Bohemian, Apr 27 '15 at 10:58
yes, coz its a temp table which i created and the column type is DATETIME. — Dragon, Apr 27 '15 at 11:02
Are you sure this is MySql and not SQL Server? if it's sql server you should probably try to convert your [Date](https://msdn.microsoft.com/en-us/library/bb630352.aspx) to [DateTime2](https://msdn.microsoft.com/en-us/library/bb677335.aspx) and not [DateTime.](https://msdn.microsoft.com/en-us/library/ms187819.aspx) — Zohar Peled, Apr 27 '15 at 11:05

WorkSmarter · Accepted Answer · 2015-04-27T11:38:18.353

1

Example of what the code would look like to convert from date to datetime2

DECLARE @d1 date;
SET @d1 = GETDATE()

-- When converting date to datetime the minutes portion becomes zero.
SELECT CAST (@d1 AS datetime2) AS [date as datetime2]

For more information about cast and convert, see the Microsoft reference.

edited Apr 27 '15 at 11:38

answered Apr 27 '15 at 11:27

WorkSmarter

3,738
3
29
34

score 1 · Answer 2 · edited May 23 '17 at 12:14

You probably get out of range exception because Date supports a date range between
January 1, 1 and December 31, 9999 , while DateTime supports a date range only between
January 1, 1753 to December 31, 9999.

Want to know Why 1753? Read this. (Recommended reading for anyone that likes trivia items)

Try converting to Datetime2 instead of Datetime, this should be OK since Datetime2 supports a date range similar to date.

the conversion can be done simply by using CAST:

SELECT CAST(YourDateTypeColumn As Datetime2)
FROM YourTable

How to convert a date field to a date time format? SQL 2008

2 Answers2