Can I change only day in datetime in SQL Server 2005?

Question

0.00/5 (No votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

Dear All,

I'm stuck in a situation where I have to update 60000 records date. I just want to update the date but that means if the date is 23.04.1980 and 10.09.1989 then I just want to update the date i.e. 23 & 10 only to date like 09.04.1980 & 09.09.1989 keeping the rest month and year untouched. Is this possible?

Please provide me solution of this. I have tried datepart to do this but that update the whole date to year 1900 which is wrong. Also dateadd not work here because the dates are different.

Posted 29-Jan-10 22:53pm

vinay.sarmalkar

Updated 2-Feb-10 8:29am

Sean Ewington

v2

Add a Solution

6 solutions

Solution 1

I do not know if this can be easly use SQL to achive this. I would put SELECT query to get a list and then for each record I would fetch into DateTime type. Then update with updated date.

Example to modify DateTime:

DateTime dt = DateTime.Now.Date.AddDays(2);
dt = dt.AddMonth(1);
dt = dt.AddYear(1);

Posted 30-Jan-10 1:47am

Saksida Bojan

Solution 2

You know, your coding would go A LOT faster if you used google:

http://msdn.microsoft.com/en-us/library/ms186724.aspx[^]

Posted 30-Jan-10 2:30am

#realJSOP

Solution 3

This query:

update table
set date = DateAdd(dd, 9-DatePart(dd, date), date)

would update the table table and set the column date to the 9th of the month leaving the month and year (and time) values untouched.

I hope this helps

Posted 31-Jan-10 23:29pm

WoutL

Solution 5

THIS IS THE TESTED SOLUTION.

update personnaldetails
set dbdate = dateadd(day,(29-datepart(dd,dbdate)),dbdate)

Posted 17-Mar-12 0:50am

vinay.sarmalkar

Solution 6

in case you want to just change the day as '9' of all records

Update TableName set yourDate = cast(Month(yourDate) as varchar(2)) + '/9/' + Cast(Year(yourdate) as varchar(4))

Posted 27-Nov-12 15:52pm

tenocsic

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vinay.sarmalkar · Accepted Answer · 2010-02-02T05:40:00

Solution 4

THIS IS THE TESTED SOLUTION.

SQL

update personnaldetails
set dbdate = dateadd(day,(29-datepart(dd,dbdate)),dbdate)

Posted 2-Feb-10 5:40am

vinay.sarmalkar