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'
ASPDEVAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
Sorry, my typing is out of whack

update table1
set datefield = '2011-03-21' + ' ' + CONVERT(varchar(8), datefield, 114)
where CONVERT(varchar(10), datefield, 120) = '2100-01-01'
or CONVERT(varchar(10), datefield, 120) = '2099-01-01
0
 
Ephraim WangoyaCommented:

update table1
set datefield = '2011-03-21 10:05:00'
where datefield = '2100-01-01 10:05:00'
0
 
ASPDEVAuthor Commented:
But I have nearly 100's of them.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Lee SavidgeCommented:
Are all the dates set to the same or are they all just very much incorrect?
0
 
ASPDEVAuthor Commented:
Most of them are set to year 2100 and 2099.
0
 
Lee SavidgeCommented:
And they all need to be set to the 21st March 2011?
0
 
ASPDEVAuthor Commented:
Correct.
0
 
Ephraim WangoyaCommented:


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'
0
 
Lee SavidgeCommented:
update mytable set mydate = dateadd(dd, DATEDIFF(dd, mydate, '2011-03-21'), mydate)
0
 
Lee SavidgeCommented:
Mine needs testing :) Not in front of SQL Management Studio to test!
0
 
Ephraim WangoyaCommented:
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'
0
 
ASPDEVAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.