Updating datetime


14-Oct-2020 09:36

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.



If they are off on the exact area the septum piercing should go through, you are unfortunately in for a bit more pain.… continue reading »


Read more

Showing films in 3D, IMAX, and standard formats, The Picture Show offers second run movies in its discount theater.… continue reading »


Read more

Disabled Dating Club is one of the fastest growing dating sites on the internet, and more and more members are signing up every day, giving you a much higher chance of finding ‘the one’.… continue reading »


Read more