Trying to set variable to last day of current mont

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 2008 Forums

Transact-SQL (2008)

Trying to set variable to last day of current mont

Author

Topic

Darts75
Starting Member

27 Posts

Posted - 2014-09-28 : 19:49:49

Hi Everyone,

I have written some SQL that is intended to populate a variable (int) called '@lastDayMonth' with the last day of the current month.

Instead of seeing a day number I am seeing unknown (NULL).

Here is my SQL...


DECLARE @currMonthNum INT,
	@lastDayMonth INT

SET @currMonthNum = DATEPART(MM, GETDATE())

IF @currMonthNum = (1 | 3 | 5 | 7 | 8 | 10 | 12)
	SET @lastDayMonth = 31
IF @currMonthNum = 2
	SET @lastDayMonth = 28
IF @currMonthNum = (4 | 6 | 9 | 11)
	SET @lastDayMonth = 30


SELECT
@currMonthNum
, @lastDayMonth

Here is a screen capture of my results in SSMS.

If anybody can shed some light on why I am seeing NULL rather than the last day of the current month it will be greatly appreciated.

Kind Regards,

David

aerovost89
Starting Member

1 Post

Posted - 2014-09-28 : 21:41:02

you can use in on your query

DECLARE @currMonthNum INT,
@lastDayMonth INT

SET @currMonthNum = DATEPART(MM, GETDATE())

IF @currMonthNum in(1,3,5,7,8,10,12)
SET @lastDayMonth = 31
IF @currMonthNum in(2)
SET @lastDayMonth = 28
IF @currMonthNum in(4,6,9,11)
SET @lastDayMonth = 30

SELECT
@currMonthNum
, @lastDayMonth

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-29 : 13:47:04

Don't hard-code like that. What about leap years? Code below uses only date math and works accurately in leap and nonleap years.


DECLARE @currMonthNum INT,
	@lastDayMonth INT
SELECT @currMonthNum = DATEPART(MONTH, GETDATE()),
    @lastDayMonth = DATEPART(DAY, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)))
SELECT @currMonthNum, @lastDayMonth
GO
--check different Feb dates
DECLARE @getdate datetime
DECLARE @currMonthNum INT,
	@lastDayMonth INT
SET @getdate = '20140210'
SELECT @currMonthNum = DATEPART(MONTH, @getdate),
    @lastDayMonth = DATEPART(DAY, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @getdate) + 1, 0)))

SET @getdate = '20120210'
SELECT @currMonthNum = DATEPART(MONTH, @getdate),
    @lastDayMonth = DATEPART(DAY, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @getdate) + 1, 0)))
SELECT @currMonthNum, @lastDayMonth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources