convert a number into 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 2000 Forums

SQL Server Development (2000)

convert a number into date

Author

Topic

cardgunner

326 Posts

Posted - 2007-11-15 : 13:29:54

In a table I have two columns
peri and year. I think I need to convert peri and year to one date field in order to select records that are greater then or less then a date.

Table A
ID peri year
a 1 2006
b 9 2005
c 11 2007
d 11 2006
d 6 2006

I need all the records before 10/01/2007 although the day is not needed.

peri is month in case you didn't know.

Card Gunner

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-15 : 13:58:20

[code]
select
Date=dateadd(month,(12*a.Year)-22801+a.peri,0)

from
(
-- Test Data
select year =2007, peri = 1 union all
select year =2008, peri = 12 union all
select year =2006, peri = 7
) a

Results:
Date
------------------------------------------------------
2007-01-01 00:00:00.000
2008-12-01 00:00:00.000
2006-07-01 00:00:00.000

(3 row(s) affected)

[/code]

CODO ERGO SUM

cardgunner

326 Posts

Posted - 2007-11-15 : 14:24:06

Holy cow it works! Not that I didn't think it would but I would never have figured that out.

What does 22801 represent?

Card Gunner

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-15 : 14:32:29

quote:
Originally posted by cardgunner
...What does 22801 represent?...

Read through this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources