Converting column contents to a new column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting column contents to a new column

Author

Topic

dlhall
Starting Member

8 Posts

Posted - 2009-09-30 : 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

38200 Posts

Posted - 2009-09-30 : 16:07:27

Datetime column can not be just 8 characters. Datetime always contains the date and the time.

So what is the data type of the column? Datetime or char/varchar? If it's datetime, then you'll at least have 00:00 in it as well.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dlhall
Starting Member

8 Posts

Posted - 2009-09-30 : 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

38200 Posts

Posted - 2009-09-30 : 18:43:41

If it's datetime, then for sure the time is included.

Ignore the designer when it says 8 is the length, it is referring to number of bytes for storage and not length of the data.

You can use the DATEPART function to get the desired format that you want.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 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

52326 Posts

Posted - 2009-10-01 : 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

22864 Posts

Posted - 2009-10-01 : 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

52326 Posts

Posted - 2009-10-01 : 03:39:36

also

select stuff(convert(varchar(10),dateadd(month,4,date_col),3),1,3,'') from table

dlhall
Starting Member

8 Posts

Posted - 2009-10-01 : 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

22864 Posts

Posted - 2009-10-01 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources