Solved

Return the last date of the month over a given period of years

Posted on 2012-09-09
321 Views
I am using the following - surely there is a better way to return the last date of the month.

In (#31/01/2009#,#28/02/2009#,#31/03/2009#,#30/04/2009#,#31/05/2009#,#30/06/2009#,#31/07/2009#,#31/08/2009#,#30/09/2009#,#31/10/2009#,#30/11/2009#,#31/12/2009#,#31/01/2010#,#28/02/2010#,#31/03/2010#,#30/04/2010#,#31/05/2010#,#30/06/2010#,#31/07/2010#,#31/08/2010#,#30/09/2010#,#31/10/2010#,#30/11/2010#,#31/12/2010#,#31/01/2011#,#28/02/2011#,#31/03/2011#,#30/04/2011#,#31/05/2011#,#30/06/2011#,#31/07/2011#,#31/08/2011#,#30/09/2011#,#31/10/2011#,#30/11/2011#,#31/12/2011#,#31/01/2012#,#29/02/2012#,#31/03/2012#,#30/04/2012#,#31/05/2012#,#30/06/2012#,#31/07/2012#,#31/08/2012#)
0
Question by:Richard Schilke

LVL 25

Accepted Solution

Should be ... is that in a SELECT statements WHERE clause?

If the column was called [a_date] ... you could do
WHERE datepart('d',dateadd('d',1,[a_date])) = 1 AND [a_date] BETWEEN #31/01/2009# AND #31/08/2012#

ie. add 1 day ... if it that is the first, it must have been the last day of the month AND in the full date range.
0

LVL 47

Expert Comment

So, you are trying to filter records so that you are only displaying those records from the last day of the month, between two dates.

You could use:

WHERE Day([DateField]) = DateSerial(Year([DateField]), Month([DateField])+1, 0)
AND [DateField] BETWEEN [StartOfPeriod] AND [EndOfPeriod]

The key to this is that when you use the DateSerial function, you increase the month by 1 and set the Day (last argument) to zero.  This will return the last day of the current month.

Oops, that technique will work for Access, but I'm not familiar enough with SQL Server to identify the SQL Server syntax for that.
0

LVL 3

Author Closing Comment

Your solution worked straight out of the blocks! thank -you expert!
0

Featured Post

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…