[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Update Month,Day,Year in SQL Datetime field

Posted on 2011-03-21
12
Medium Priority
?
724 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

649 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