ms sql + update datetime entry

I have a bunch of dates that entered in a table incorrectly, for March 11th, 2011, it entered 11/3/2011, is there a way to update these entries with and update statement, but only changing the date part and keeping the time part the same, as each entry has a time as well as the date.
dkilbyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick MatthewsConnect With a Mentor Commented:
No, that was just a demonstration.  You can simply use this, substituting the actual table/column names:

UPDATE [TableName]
SET [DateColumn] = DATEADD(day, DATEDIFF(day, '2011-11-03', '2011-03-11'), [DateColumn])
WHERE [DateColumn] >= '2011-11-03' AND [DateColumn] < '2011-11-04'

Open in new window

0
 
Patrick MatthewsCommented:
If you ONLY want to take the entries where the date portion is 2011-11-03 and cast them back to 2011-03-11...



CREATE TABLE #foo (Dt datetime)

INSERT INTO #foo (Dt) VALUES ('2011-01-15 11:22:33')
INSERT INTO #foo (Dt) VALUES ('2011-03-10 19:47:01')
INSERT INTO #foo (Dt) VALUES ('2011-07-15 11:22:33')
INSERT INTO #foo (Dt) VALUES ('2011-11-03 06:30:00')

SELECT Dt FROM #foo ORDER BY Dt

UPDATE #foo
SET Dt = DATEADD(day, DATEDIFF(day, '2011-11-03', '2011-03-11'), Dt)
WHERE Dt >= '2011-11-03' AND Dt < '2011-11-04'

SELECT Dt FROM #foo ORDER BY Dt

DROP TABLE #foo

Open in new window

0
 
dkilbyAuthor Commented:
I have a lot of rows, do i have to insert each row into the temp table one row at a time?
0
 
dkilbyAuthor Commented:
thank you
0
All Courses

From novice to tech pro — start learning today.