Solved

Update Month,Day,Year in SQL Datetime field

Posted on 2011-03-21
12
708 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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 500 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Duplicated data in GROUP_CONCAT 2 50
T-SQL: Please describe what a page split is 5 58
Oracle - BLOB Extract Line 2 15
Create a Calendar table 29 43
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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