0

I need to change the data type for a column from Datetime2(7) to Datetime where I have a lot of records with that format, so when I try to change the Data type I get the following error:

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

How can I change the data and then change the data type for that column? The reason I need to switch to a less precise data type to for a application where it is not compatible with. This application connects directly so no conversion can be done in code. I am working inside SSMS.

Fixed one column with:

select *, cast(calldate as datetime)) * from records

But not for the other column using same Cast - it gives the out-of range error. Maybe some fields are null - would that be a problem? If so I tried this(same error):

select *, cast(closedate as datetime) from records where closedate is not null
OneFineDay
  • 9,004
  • 3
  • 26
  • 37
  • Try looking here. http://stackoverflow.com/questions/11081571/how-to-cast-datetime2-as-datetime. Edit: In a nut shell, use Datepart to split the datetime2 into pieces and use those pieces to create a Datetime – gmalenko Sep 18 '14 at 03:39
  • I saw that one, I just don't understand the code. – OneFineDay Sep 18 '14 at 03:41
  • I fixed one column, now the other is in error. – OneFineDay Sep 18 '14 at 04:05
  • Guessing this post is more likely the source of your out of range error. http://stackoverflow.com/questions/16197735/out-of-range-error-converting-datetime2-to-datetime datetime2 also supports earlier dates than datetime - not just more precision. (i.e. 1/1/0001) – RThomas Sep 18 '14 at 04:16
  • @RThomas, saw that too, no dates would be out of range, but they might be null. Thanks though! – OneFineDay Sep 18 '14 at 04:19

1 Answers1

0
DECLARE @TestTime as DateTime2
 SET @TestTime = (SELECT CAST(GetDate() as datetime2))

 SELECT CAST(@TestTime AS datetime)

When I ran it @TestTime = "2014-09-24 15:39:02.7970000" after the first set.
The result was "2014-09-24 15:39:02.797"

gmalenko
  • 162
  • 4
  • 16