SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Converting column contents to a new column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dlhall
Starting Member

7 Posts

Posted - 09/30/2009 :  16:02:17  Show Profile  Reply with Quote
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
37460 Posts

Posted - 09/30/2009 :  16:07:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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."
Go to Top of Page

dlhall
Starting Member

7 Posts

Posted - 09/30/2009 :  18:05:39  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37460 Posts

Posted - 09/30/2009 :  18:43:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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."
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 10/01/2009 :  02:19:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/01/2009 :  02:44:10  Show Profile  Reply with Quote
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)
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 10/01/2009 :  03:22:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/01/2009 :  03:39:36  Show Profile  Reply with Quote
also

select stuff(convert(varchar(10),dateadd(month,4,date_col),3),1,3,'') from table
Go to Top of Page

dlhall
Starting Member

7 Posts

Posted - 10/01/2009 :  09:57:35  Show Profile  Reply with Quote
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...
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 10/01/2009 :  10:07:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000