Updating datetime field from a date field throws error in SQL Server

0
votes

I am trying to update a datetime field on a table (MyTable1) from a date field on another table (MyTable2).

  • DateTime value in datetime field in MyTable1 is stored following below format yyyy-mm-dd HH:mm:ss.fff
  • Date value in date field in MyTable2 is stored following below format yyyy-mm-dd

So taking into account this, I perform below 2 attempts without success. What am I doing wrong?

ATTEMPT #1:

UPDATE tblToUpdate
   SET tblToUpdate.DateTimeField = fromTbl.DateField
  FROM MyTable1 tblToUpdate INNER JOIN MyTable2 fromTbl on tblToUpdate.Id = fromTbl.Id

This produces below error:

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

ATTEMPT #2:

UPDATE tblToUpdate
   SET tblToUpdate.DateTimeField = (case when fromTbl.DateField is NULL 
                                           then NULL 
                                           else format(fromTbl.DateField, 'yyyy-mm-dd HH:mm:ss.fff') end)
  FROM MyTable1 tblToUpdate INNER JOIN MyTable2 fromTbl on tblToUpdate.Id = fromTbl.Id

This produces below error:

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

2
sql-serverdatedatetimecastingtype-conversion
Date datatypes don't have a format - HoneyBadger
The error is VERY clear. One of those columns is string and one is date/datetime - contrary to your description. So - which one is it? - SMor
And i'll note that the error might also be thrown by the attempt to join on dissimilar datatypes for Id. Show the DDL for the tables. A trigger might also be the source of the problem - this would be apparent if you included all of the error details. - SMor
What is you smallest date value? Data type DATE has a range of accepted values from 01-01-0001 through 12-31-9999, data type DATETIME has a range of accepted values from 01-01-1753 through 12-31-9999. - HoneyBadger
@user1624552, you can just SELECT MIN(DateField) FROM yourTable. If the result is < 01-01-1753 that's probably the issue. - HoneyBadger

2 Answers

0
votes

yyyy-mm-dd HH:mm:ss.nnn isn't an ambiguous format with the datetime datatype. The only 2 are yyyyMMdd and yyyy-MM-ddThh:mm:ss.nnn. I also suggest against FORMAT, as it's an awfully show function. If you must supply a non-ambiguous varchar value, use CONVERT and a style code:

CONVERT(varchar(23),fromTbl.DateField,126)

This will supply a varchar value in the format yyyy-MM-ddThh:mm:ss.nnn. For example, with GETDATE() I get the value '2019-11-27T12:32:27.763' right now.

0
votes

You have to convert the values to the same as you can only substitute the new date with the old date, if they are the same timeformat. Try something like this.

UPDATE tblToUpdate
   SET tblToUpdate.DateTimeField = CONVERT(datetime,fromTbl.DateField)
  FROM MyTable1 tblToUpdate INNER JOIN MyTable2 fromTbl on tblToUpdate.Id = fromTbl.Id