?
Solved

Update Month,Day,Year in SQL Datetime field

Posted on 2011-03-21
12
Medium Priority
?
719 Views
Last Modified: 2012-05-11
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'
0
Comment
Question by:ASPDEV
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 4
12 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35181711

update table1
set datefield = '2011-03-21 10:05:00'
where datefield = '2100-01-01 10:05:00'
0
 

Author Comment

by:ASPDEV
ID: 35181719
But I have nearly 100's of them.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35181732
Are all the dates set to the same or are they all just very much incorrect?
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

Author Comment

by:ASPDEV
ID: 35181748
Most of them are set to year 2100 and 2099.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35181765
And they all need to be set to the 21st March 2011?
0
 

Author Comment

by:ASPDEV
ID: 35181791
Correct.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35181806


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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35181812
update mytable set mydate = dateadd(dd, DATEDIFF(dd, mydate, '2011-03-21'), mydate)
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 35181826
Mine needs testing :) Not in front of SQL Management Studio to test!
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35181843
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
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35181858
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
 

Author Closing Comment

by:ASPDEV
ID: 35182115
Thanks.
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question