SQL - First Month of Year and Last day of previous month.

Posted on 2013-08-27
Medium Priority
Last Modified: 2013-08-30
I have a need to pull the first month of current year and then also pull the previous month end.


1/1/2013 - 7/31/2013

I'm doing a parameter with a report and need to have this date automatically populated.  I am able to get the end of month for last month.  However, I can't seem to get the first month for the year at beginning of month.
Question by:holemania
LVL 74

Expert Comment

ID: 39443858
select trunc(sysdate,'yyyy'), trunc(sysdate,'mm') - 1 from dual
LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 39444438
wanting "last day of month" is often associated with selection of date ranges

and further this is also often due to use of "between ...  and ..."

a simpler (and more reliable) date range selection method is to use  
a combination of >= with <

so instead of "end of month for last month" you can use "less than 1st of this month" e.g.
-- Oracle syntax
from <<whatever>>
where ( date_field >=  trunc(sysdate,'yyyy') and date_field < trunc(sysdate,'mm') )
and ...

-- MS SQL syntax
from <<whatever>>
where ( date_field >=  dateadd(YEAR, datepart(YEAR,getdate())-1900, 0)
       and date_field < dateadd(DAY, -(DAY(getdate())- 1), dateadd(DAY, datediff(DAY,0, getdate() ),0))
and ...

Open in new window

for more on the topic of "between" please see: "Beware of Between"

fyi: in mssql for last day of last month:
dateadd(DAY, -(DAY(getdate())), dateadd(DAY, datediff(DAY,0, getdate() ),0))

if the need isn't for either Oracle/SQL Server please identify dbms type/version

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
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…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…

607 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