| Author |
 Topic  |
|
|
dlhall
Starting Member
7 Posts |
 Posted - 09/30/2009 : 16:02:17
|
I have a datetime column (8 characters) that contains a date in the format of mm/dd/yyyy. I need to leave that column intact, but convert the contents to a mm/yy format into a new column as a varchar (8), and add 4 to the months.
So, if the date in column a (datetime) is 1/2/2009, I need the new column to be 05/09.
Anybody??
Thanks |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
dlhall
Starting Member
7 Posts |
Posted - 09/30/2009 : 18:05:39
|
It is a Datetime column, 8 characters in length, and contains the info as described above. There is no time in this column only a date. This is a copy of the DB design...
1 formDate datetime 8 0 |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 10/01/2009 : 02:19:33
|
quote:
Originally posted by dlhall
I have a datetime column (8 characters) that contains a date in the format of mm/dd/yyyy. I need to leave that column intact, but convert the contents to a mm/yy format into a new column as a varchar (8), and add 4 to the months.
So, if the date in column a (datetime) is 1/2/2009, I need the new column to be 05/09.
Anybody??
Thanks
Why do you want to do this?
Madhivanan
Failing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 10/01/2009 : 02:44:10
|
quote:
Originally posted by dlhall
I have a datetime column (8 characters) that contains a date in the format of mm/dd/yyyy. I need to leave that column intact, but convert the contents to a mm/yy format into a new column as a varchar (8), and add 4 to the months.
So, if the date in column a (datetime) is 1/2/2009, I need the new column to be 05/09.
Anybody??
Thanks
i guess this is for some display purpose. if yes, use like
right('0'+ cast(month(dateadd(mm,4,yourdatecol)) as varchar(2)),2) + '/' + datename(yy,dateadd(mm,4,yourdatecol) |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 10/01/2009 : 03:22:09
|
quote:
Originally posted by visakh16
quote:
Originally posted by dlhall
I have a datetime column (8 characters) that contains a date in the format of mm/dd/yyyy. I need to leave that column intact, but convert the contents to a mm/yy format into a new column as a varchar (8), and add 4 to the months.
So, if the date in column a (datetime) is 1/2/2009, I need the new column to be 05/09.
Anybody??
Thanks
i guess this is for some display purpose. if yes, use like
right('0'+ cast(month(dateadd(mm,4,yourdatecol)) as varchar(2)),2) + '/' + datename(yy,dateadd(mm,4,yourdatecol)
Extra brace is missing
It is only for display, you can use
select right(convert(varchar(10),dateadd(month,4,date_col),103),7) from the table
Madhivanan
Failing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47036 Posts |
Posted - 10/01/2009 : 03:39:36
|
also
select stuff(convert(varchar(10),dateadd(month,4,date_col),3),1,3,'') from table |
|
|
|
dlhall
Starting Member
7 Posts |
Posted - 10/01/2009 : 09:57:35
|
Madhivanan - Your solution worked, but is giving the results as 05/2009 rather than 05/09. How would I get the desired result format?
Thanks for your help here... |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 10/01/2009 : 10:07:44
|
Use the one Visakh posted, or this
select right(convert(varchar(10),dateadd(month,4,date_col),3),5) from your_table
Madhivanan
Failing to plan is Planning to fail |
Edited by - madhivanan on 10/01/2009 10:08:30 |
|
|
| |
Topic |
|
Converting column contents to a new column
