Solved

Update Month,Day,Year in SQL Datetime field

Posted on 2011-03-21
12
699 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
  • 4
  • 4
  • 4
12 Comments
 
LVL 32

Expert Comment

by:ewangoya
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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:ewangoya
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:ewangoya
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:
ewangoya 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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