convert numeric to date - 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert numeric to date

Author

Topic

partitur
Starting Member

12 Posts

Posted - 2009-09-11 : 09:21:23

Hey guys!

I have an easy one for you:
I have a table that contains a numeric field (PERIOD) that is representing year and month. It consists of 5 digits and is 1YYMM:
First digit: is always a 1
2-3 digit: year, decennial
4-5 digit: month

For example:
10909=2009SEP
10708=2007AUG
11001=2010JAN
10212=2002DEC

How can I in the select convert this filed into a date format with year in numbers and month in text?
Ex: 10909 should come out as 2009SEP

Thanks : )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-11 : 09:47:21

See if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

partitur
Starting Member

12 Posts

Posted - 2009-09-11 : 10:34:55

quote:
Originally posted by madhivanan

See if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164

Madhivanan

Failing to plan is Planning to fail

Thanks : ) But I can't really get it to work with the substring function since my data is numeric.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-11 : 11:01:55

Well this has nothing to do with dates really. It is just taking an encoded int and re-coding it to a string. You still can't use it as a date. But see if this works for you:


select '20' + substring(convert(char(5), n), 2, 2)
       + upper(left(datename(month,'1900-' + right(n,2) + '-01') ,3))
from   ( --Your Table
       select 10909 n union all
       select 10708 union all
       select 11001 union all
       select 10212
       ) d

output:
2009SEP
2007AUG
2010JAN
2002DEC

Be One with the Optimizer
TG

partitur
Starting Member

12 Posts

Posted - 2009-09-11 : 11:27:09

quote:
Originally posted by TG

Well this has nothing to do with dates really. It is just taking an encoded int and re-coding it to a string. You still can't use it as a date. But see if this works for you:

select '20' + substring(convert(char(5), n), 2, 2)
+ upper(left(datename(month,'1900-' + right(n,2) + '-01') ,3))
from ( --Your Table
select 10909 n union all
select 10708 union all
select 11001 union all
select 10212
) d

output:
2009SEP
2007AUG
2010JAN
2002DEC

Be One with the Optimizer
TG

Beautiful. Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-14 : 02:27:47

Ok. Why dont you use proper Datetime datatype to store dates?
What you want seems the presentation issue

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources