Update a date field to a specific day and time

Question

Update a date field to a specific day and time

Rog Saber

SSCrazy

Points: 2548
More actions
November 3, 2010 at 9:34 am

#231207

I have a temp table in which I want to update a date field. If the date field is after 07:00, I want to update it to 06:59 of the next day. Can I do this in one update statement or am I going to have to use a cursor and manipulate it row by row?
Here's an example of what I am looking for:
date_field date_field after update
2010-06-24 18:19:00.0002010-06-25 06:59:59.000
2010-06-20 01:50:00.0002010-06-20 01:50:00.000 (no update performed becuase if is before 07:00)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95405 More actions · Answer 1

~~See the attached code (having trouble posting from work). I'll copy the code into this message tonight when I get home.~~

DECLARE @test-2 TABLE (MyDate datetime);

INSERT INTO @test-2

SELECT '2010-06-24 18:19:00.000' UNION ALL

SELECT '2010-06-20 01:50:00.000';

SELECT MyDate ,

NewDate = CASE WHEN MyDate > DATEADD(day, DateDiff(day, 0, MyDate), '07:00')

THEN DATEADD(day, DateDiff(day, 0, MyDate)+1, '06:59:59')

ELSE MyDate END

FROM @test-2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2