13,560,309 members
See more: , +
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
Updated 2-Feb-10 8:29am
v2

## Solution 4

THIS IS THE TESTED SOLUTION.

```update personnaldetails

## 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);

## Solution 2

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

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

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

## Solution 5

THIS IS THE TESTED SOLUTION.

update personnaldetails

## 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))

Top Experts
Last 24hrsThis month
 OriginalGriff 115 Richard MacCutchan 70 Dave Kreskowiak 60 Thaddeus Jones 60 Simonetos The Greek 55
 OriginalGriff 6,224 ppolymorphe 2,757 Richard MacCutchan 2,727 Maciej Los 2,055 Wendelius 1,905