November 3, 2010 at 9:34 am
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)
November 3, 2010 at 10:05 am
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
November 3, 2010 at 10:28 am
CREATE TABLE #MyTempTable (date_field datetime)
INSERT INTO #MyTempTable(date_field)
SELECT '2010-06-24T18:19:00.000' UNION ALL
SELECT '2010-06-20T01:50:00.000'
UPDATE #MyTempTable
SET date_field = DATEADD(day, DATEDIFF(day, '1753-01-01T00:00:00.000', date_field), '1753-01-02T06:59:00.000')
WHERE DATEPART(hour, date_field) >= 7
November 3, 2010 at 10:35 am
These worked great - thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply