CODEWITHSUNDEEP
sql-serversql-server-2008t-sqldatetime
mann
mann

Reputation: 184

Update query to find and change portion of MS SQL DateTime

I have various datetime stamps on a expiration field (datetime). The datetime in that field for each record varies. For example, 

2015-12-31 04:59:00:000, 2014-12-31 17:00:00:000, 2020-12-15 04:00:00:000

Trying to write a query to find all datetime that have an ENDING that is not equal != to 05:00:00:000.

Then an update query to update the end of the stamp to 05:00:00:000, leaving the front as is.

Some example changes, from - to:

From: 

2015-12-31 04:59:00:000, 2014-12-31 17:00:00:000, 2020-12-15 04:00:00:000

To: 

2015-12-31 05:00:00:000, 2014-12-31 05:00:00:000, 2020-12-15 05:00:00:000

Here is what I'd like to see:

UPDATE table_name
SET table_name.expire_field = keep_front_date_portion + '05:00:00.000'
WHERE table_name.expire_field = date_portion_ignore and time_portion != '05:00:00.000'

Not sure how this would be written correctly in MS SQL 2008 syntax?

I found this post but from what I can tell it needs a timestamp that does not vary.

Upvotes: 1

Views: 80

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

We can use DATEADD/DATEDIFF to reset just the time portion of a datetime value

UPDATE table_name
SET expire_field = 
   DATEADD(day,DATEDIFF(day,'19000101',expire_field),'1900-01-01T05:00:00')
WHERE DATEPART(hour,expire_field) != 5 or
      DATEPART(minute,expire_field) != 0 or
      DATEPART(second,expire_field) != 0

But you may find it easier to just skip the WHERE clause and let it update the entire table - you're not going to be able to benefit from indexes here anyway.

In the DATEADD/DATEDIFF pair, the date is arbitrary. First, the inner DATEDIFF asks "how many whole days have passed since some date", and then the DATEADD adds that same number of days onto some date at 5am.

Upvotes: 1

Related Questions