how to update DATE column (month only)in a table

[bgrissom]

I just used the script that someone posted to change just the year in a date field and it worked great. I want to do the samething by just changing the month only
thanks

[ck9663]

I just used the script that someone posted to change just the year in a date field and it worked great. I want to do the samething by just changing the month only
thanks

use dateadd to add (or subtract) specific unit of date/time to a date time field...

for hardcoding the date, fix the month as literal string and use datepart to get the day and year...

this script will force today's month to december (12)

select cast('12/' + cast(datepart(day,getdate()) as char(2)) + '/' + cast(datepart(year,getdate()) as char(4)) as datetime)

-- CK

[bgrissom]

Thanks for your reply. Sorry i'm pretty new to SQL and dont fully understand your script. Here is the script that i ran to change the year from 07 to 08. I want to run the same script, but change all the months 1,2,3,4,5,6,7,8,910,11,12 to 1 for January.

update orderline
set ord_date='2008-'+RIGHT('0'+MONTH(ord_date),2)+'-'+RIGHT('0'+DAY(ord_date),2)

[ck9663]

Thanks for your reply. Sorry i'm pretty new to SQL and dont fully understand your script. Here is the script that i ran to change the year from 07 to 08. I want to run the same script, but change all the months 1,2,3,4,5,6,7,8,910,11,12 to 1 for January.

update orderline
set ord_date='2008-'+RIGHT('0'+MONTH(ord_date),2)+'-'+RIGHT('0'+DAY(ord_date),2)

are you trying to change the format of your date? from 2008/01/08 to 2008 January 08 ? i recommend you handle that on the client-side, not in the db-side. if you really have to, format your field into something else, you can keep the datetime field on your table, just change the way your date looks...

here's a good reference


-- CK

[bgrissom]

No i dont want to change the format. All i want to do is change the month of the date for all records in a table ex:

02/02/2008
03/04/2008
04/12/2008
05/15/2008

I want to leave the date in the same format. I just want to change the months 02,03,04,05 to be 01 thats it so when the script is done it will look like this.

01/02/2008
01/04/2008
01/12/2008
0115/2008

Thanks for your help

[ck9663]

No i dont want to change the format. All i want to do is change the month of the date for all records in a table ex:

02/02/2008
03/04/2008
04/12/2008
05/15/2008

I want to leave the date in the same format. I just want to change the months 02,03,04,05 to be 01 thats it so when the script is done it will look like this.

01/02/2008
01/04/2008
01/12/2008
0115/2008

Thanks for your help

try this first:

select mycolumn as fromthis, '01/' + right('0' + cast(datepart(day,mycolumn) as char(2)),2) +
cast(datepart(year,mycolumn) as char(4) as tothis
from mytable

see if your this is what you want..once you run the update, there's not turning back unless you have transaction defined or backup tables...if you're sure about the result, run this:

update mytable
set mycolumn = '01/' + right('0' + cast(datepart(day,mycolumn) as char(2)),2) +
cast(datepart(year,mycolumn) as char(4)


-- CK