Link to home
Start Free TrialLog in
Avatar of dkilby
dkilbyFlag for Canada

asked on

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.
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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

Avatar of dkilby

ASKER

I have a lot of rows, do i have to insert each row into the temp table one row at a time?
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dkilby

ASKER

thank you