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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -