?
Solved

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

Posted on 2012-09-09
3
Medium Priority
?
384 Views
Last Modified: 2012-09-11
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
Comment
Question by:Richard Schilke
3 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 400 total points
ID: 38381800
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 49

Expert Comment

by:Dale Fye
ID: 38382590
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

by:Richard Schilke
ID: 38388864
Your solution worked straight out of the blocks! thank -you expert!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question