If I'm querying with date criteria, I always specify date ranges, as opposed to exact moments in time.
The fact that your updates are not working does not mean that the ISO format is wrong, but rather some invisible rounding errors are taking place during the comparison.
I need to change the date to 3/7/1995 but retain the time field which is different for each record.
RD "Hugo Kornelis" Hi RDRaider, You're using an ambiguous date format. DECLARE @dt datetime SET @dt = '1995-12-03T' SELECT DATEADD(month, 7 - month(@dt), @dt) -- Or did you mean this?
Granted, I was not able to reproduce the tag update failure with Igitur's code either, but I'm pretty sure it has to do with Correct. Also it's great that you posted that last date example because it also accentuates other dangers of using region-specific date formats.
Here's another test: r4912 now uses ISO format without the "T" in the middle.
SET @dt = '1995-03-12T' SELECT DATEADD(day, 7 - day(@dt), @dt) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) Thanks for the reply.
When editing and posting a change to a Date Time column in MS SQL, the following statement is executed: UPDATE My Table SET "Start Date"='30/01/2015 ' WHERE "Id"=59; which leads to an error: /* SQL Error (3621): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value The statement has been terminated.
You'll find that SQL Server cannot process Date Time values with millisecond accuracy.