Trying to set variable to last day of current month, but seeing NULL instead of 28, 30 or 31?

Question

Post reply

Trying to set variable to last day of current month, but seeing NULL instead of 28, 30 or 31?

david.dartnell

Ten Centuries

Points: 1276
More actions
September 28, 2014 at 5:49 pm

#296841

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

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63428 More actions · Answer 1

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 = 2

SET @lastDayMonth = 28

IF @currMonthNum IN (4, 6, 9, 11)

SET @lastDayMonth = 30

SELECT

@currMonthNum

, @lastDayMonth

david.dartnell Ten Centuries Points: 1276 More actions · Answer 2

david.dartnell

Ten Centuries

Points: 1276

September 28, 2014 at 7:16 pm

#1748697

Thank you, using IN did the trick!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

If you're looking for a simple and reliable means of obtaining the last date of the current month, try this:

SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),0))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

pietlinden (9/28/2014)
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 = 2
SET @lastDayMonth = 28
IF @currMonthNum IN (4, 6, 9, 11)
SET @lastDayMonth = 30
SELECT
@currMonthNum
, @lastDayMonth

This is not going to work for an overlapping year. Please follow the ChrisM@Work solution.

Igor Micev,My blog: www.igormicev.com

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

Igor Micev (9/29/2014)
pietlinden (9/28/2014)
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 = 2
SET @lastDayMonth = 28
IF @currMonthNum IN (4, 6, 9, 11)
SET @lastDayMonth = 30
SELECT
@currMonthNum
, @lastDayMonth
This is not going to work for an overlapping year. Please follow the ChrisM@Work solution.

SELECT MONTH(DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),0))) [Month],

DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),0)) [Last date]

Igor Micev,My blog: www.igormicev.com

below86 SSChampion Points: 11513 More actions · Answer 6

How about one more version?

SELECT DATEPART(DD, DATEADD(DD, -1, DATEADD(MM, 1, DATEADD(DD, -(DATEPART(DD, GETDATE()) -1), GETDATE()))))

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Greg Goss SSCrazy Points: 2160 More actions · Answer 7

This doesn't explicitly answer the question, but it is something you can consider. I keep a CALENDAR table in many of my databases. It can be customized to crazy levels, but the core idea is that you have a table that you populate with a row for each historical and future date that you might need to deal with. I personally go 10 years back and 10 years forward.

Then for each datetime value, you add columns to describe something about that day. For example '2/5/2014' is the 5th day of the second month of the year. Or the 36th day of the year. Or it's a Wednesday, or the 3rd day of the second financial period of the month as determined by my company, or that the next day is '2/6/2014' or that the previous day is '2/4/2014'. There are a very large number of ways to use a table like this to answer questions like yours. For example, if you had a calendar table, you could do queries like this to answer your question:

SELECT max(dayOfTheMonth)

FROM CALENDAR

WHERE monthName = 'February'

If you're interested, there are many helpful and well written posts on sqlservercental.com about the subject. Just do a search on "calendar table" and see what comes up!

-G

below86 SSChampion Points: 11513 More actions · Answer 8

pietlinden (9/28/2014)
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 = 2
SET @lastDayMonth = 28
IF @currMonthNum IN (4, 6, 9, 11)
SET @lastDayMonth = 30
SELECT
@currMonthNum
, @lastDayMonth

What about leap years? February has 29 days then. I really wouldn't use this unless you add something to take into account leap years.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 9

I would just look at Lynn's common date functions at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. From that, you can get to almost any date you're after relative to today.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

david.dartnell (9/28/2014)
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

Here is an easy way to get the last day of the current month. The code below demonstrates returning the entire date and just the Day.

declare @ThisDate datetime;

set @ThisDate = getdate();

select dateadd(month,datediff(month,0,@ThisDate) + 1,-1), day(dateadd(month,datediff(month,0,@ThisDate) + 1,-1))

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

pietlinden (9/28/2014)
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 = 2
SET @lastDayMonth = 28
IF @currMonthNum IN (4, 6, 9, 11)
SET @lastDayMonth = 30
SELECT
@currMonthNum
, @lastDayMonth

That's going to give a very interesting result in February 2016....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

GilaMonster (9/30/2014)
pietlinden (9/28/2014)
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 = 2
SET @lastDayMonth = 28
IF @currMonthNum IN (4, 6, 9, 11)
SET @lastDayMonth = 30
SELECT
@currMonthNum
, @lastDayMonth
That's going to give a very interesting result in February 2016....

Yes, I noticed that as well. Using actual date calculations takes that into consideration.

david.dartnell Ten Centuries Points: 1276 More actions · Answer 13

Hi ChrisM@Work,

I am trying to better understand your solution, towards this end I have started with the inner most brackets -

SELECT DATEDIFF(MONTH, 0, GETDATE())

Upon first inspection I expected this part of the query to return the numbers of months between 0 and the current month; it is October (where I am) as I write this so I was expecting a result of 10.

However after plugging this query into SSMS I was surprised to see a result of 1377!!!

Can you please explain what this query is actually doing?

Kind Regards,

David

Mohit Dhiman Say Hey Kid Points: 677 More actions · Answer 14

SELECT DATEADD(m,DATEDIFF(m,'19001231',GETDATE()),'19001231')

This will give you the month end date for the current month (which can be changed by replacing GETDATE() with whichever other month-date you want)..

And you don't have to worry about leap year here..

SELECT DATEPART(d,DATEADD(m,DATEDIFF(m,'19001231',GETDATE()),'19001231'))

This of course if you want the day number instead of the whole date. (Just extract whichever part of the date you want.

I always use this method whenever i have to work with dates to find start/end of year/month/week.

"The price of anything is the amount of life you exchange for it" - Henry David Thoreau