ASPDEV
asked on
Update Month,Day,Year in SQL Datetime field
Hello Experts,
How can I update Month,Day and Year in Datetime field , I'm using SQL server 2005.
Some weird datetime record entry in database messed up my reports, the time entry is correct.But date is wrong.
I got this '2100-01-01 10:05:00', it needs to be '2011-03-21 10:05:00'
How can I update Month,Day and Year in Datetime field , I'm using SQL server 2005.
Some weird datetime record entry in database messed up my reports, the time entry is correct.But date is wrong.
I got this '2100-01-01 10:05:00', it needs to be '2011-03-21 10:05:00'
ASKER
But I have nearly 100's of them.
Are all the dates set to the same or are they all just very much incorrect?
ASKER
Most of them are set to year 2100 and 2099.
And they all need to be set to the 21st March 2011?
ASKER
Correct.
update table1
set datefield = '2011-03-21' + ' ' + CONVERT(varchar(8), @mydate, 114)
where CONVERT(varchar(10), datefile, 120) = '2100-01-01'
or CONVERT(varchar(10), datefile, 120) = '2099-01-01'
update mytable set mydate = dateadd(dd, DATEDIFF(dd, mydate, '2011-03-21'), mydate)
Mine needs testing :) Not in front of SQL Management Studio to test!
Did'nt clean up properly
replace @mydate with datefield
update table1
set datefield = '2011-03-21' + ' ' + CONVERT(varchar(8), datefield, 114)
where CONVERT(varchar(10), datefile, 120) = '2100-01-01'
or CONVERT(varchar(10), datefile, 120) = '2099-01-01'
replace @mydate with datefield
update table1
set datefield = '2011-03-21' + ' ' + CONVERT(varchar(8), datefield, 114)
where CONVERT(varchar(10), datefile, 120) = '2100-01-01'
or CONVERT(varchar(10), datefile, 120) = '2099-01-01'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.
update table1
set datefield = '2011-03-21 10:05:00'
where datefield = '2100-01-01 10:05:00'