Puzzling query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Puzzling query

Author

Topic

Johnph
Posting Yak Master

103 Posts

Posted - 2012-08-10 : 14:06:02

I have a column called date with data that looks like this:

2012-01-01
2011-01-01
This is in DATE format not varchar.
I want to create a computed column from these fields to look like this (I want this to be VARCHAR):

2120101
2110101

I have the concatenate statement that will give me the numbers:

SELECT substring (cast( Date as varchar(10)), 1, 1) + 
substring(cast( Date as varchar(10)), 3, 2) + 
substring(cast( Date as varchar(10)), 6, 2) +
substring(cast( Date as varchar(10)), 9, 2)
FROM TestTable

This query gives me this:
2120101
2110101
(which is the correct format)

Now I try to create the computed column to look like this:

ALTER TABLE TestTable ADD testcolumn as substring (cast( StartDate as varchar(10)), 1, 1) + 
substring(cast( StartDate as varchar(10)), 3, 2) + 
substring(cast( StartDate as varchar(10)), 6, 2) +
substring(cast( StartDate as varchar(10)), 9, 2)

Instead of giving me:
2120101
2110101

this column gives me:
Jn 1 01
Jn 1 01

I am beyond puzzled

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 14:20:57

Use convert instead of cast with style 112:

ALTER TABLE #tmp ADD testcolumn as SUBSTRING(convert( char(8),Date ,112), 1, 1) + 
SUBSTRING(convert( char(8),Date ,112), 3, 2) + 
SUBSTRING(convert( char(8),Date ,112), 5, 2) + 
SUBSTRING(convert( char(8),Date ,112), 7, 2)

Johnph
Posting Yak Master

103 Posts

Posted - 2012-08-10 : 14:28:47

Trying this now

Johnph
Posting Yak Master

103 Posts

Posted - 2012-08-10 : 14:32:59

Sunitabeck, you are AMAZING!!!!!!!! thank you so much.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 14:46:30

Heh! Not really :) If some of the real experts were around, they would suggest something simpler - for example, like this:

ALTER TABLE #tmp ADD testcolumn2 as STUFF(convert( char(8),Date ,112), 2,1,'')

(Untested. I had tested the other).

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-08-16 : 08:59:22

Why do you need DATE in this format?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources