Build End of Month Date field from Year and Month

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)

Build End of Month Date field from Year and Month

Author

Topic

jmiskey
Starting Member

15 Posts

Posted - 2008-04-01 : 11:03:35

I have a database that has two fields: MONTH_ID and YEAR_ID. From these two variables, I need to build a month ending date.

So, for example, if I had
MONTH_ID: 12
YEAR_ID: 2006
I would like to return 12/31/2006

Likewise, if I had:
MONTH_ID: 2
YEAR_ID: 2008
I would like to return 2/29/2008

Can anyone help me create a formula to do this?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 11:08:44

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86769

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-04-01 : 11:11:18

[code]
select
LastDayOfMonth = dateadd(month,((yr-1900)*12)+mn,-1)
from
(
--Test Data
select yr=2008,mn=1 union all
select yr=2008,mn=2 union all
select yr=2008,mn=3 union all
select yr=2008,mn=4 union all
select yr=2008,mn=5 union all
select yr=2008,mn=6 union all
select yr=2008,mn=7 union all
select yr=2008,mn=8 union all
select yr=2008,mn=9 union all
select yr=2008,mn=10 union all
select yr=2008,mn=11 union all
select yr=2008,mn=12 union all
select yr=2009,mn=1
) a

Results:
LastDayOfMonth
------------------------
2008-01-31 00:00:00.000
2008-02-29 00:00:00.000
2008-03-31 00:00:00.000
2008-04-30 00:00:00.000
2008-05-31 00:00:00.000
2008-06-30 00:00:00.000
2008-07-31 00:00:00.000
2008-08-31 00:00:00.000
2008-09-30 00:00:00.000
2008-10-31 00:00:00.000
2008-11-30 00:00:00.000
2008-12-31 00:00:00.000
2009-01-31 00:00:00.000

(13 row(s) affected)

[/code]

CODO ERGO SUM

jmiskey
Starting Member

15 Posts

Posted - 2008-04-01 : 11:25:42

Peso, the only problem is that it looks like your code works of a given date field. I don't have a date field, just a month and a year. So, I first have to create the date before I can apply those functions to it. And that is actually the part I am having problems with. It is easy in Access (using DateSerial, but T-SQL does not have this function).

Michael, it looks like your solution does exactly what I need it to do. Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-01 : 12:24:33

SELECT DATEADD(dd,-1,CAST(('01/'+CASE WHEN ((MONTH_ID+1) %12) >10 THEN CAST(((MONTH_ID+1) %12) AS varchar(2)) ELSE '0'+ CAST(((MONTH_ID+1) %12) AS varchar(1)) END + '/'+CAST(YEAR_ID AS varchar(4))) AS datetime))

marsherfu
Starting Member

4 Posts

Posted - 2008-04-01 : 13:56:01

DateADD(Day,-1,DateAdd(Month,1,CONVERT(varchar,@m)+'/01/'+CONVERT(varchar,@y)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources