• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

Start and End Date For Previous Month

Hi Experts,

How do you get the first and last day of the previous month based on the getdate()?

today is 11/18/2008, so

the Start Date (first day of the previous month) = 10/01/2008
the End Date (last day of the previous month) = 10/31/2008

This is for MS-SQL 2005.

TIA!
0
allanau20
Asked:
allanau20
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select dateadd(day, 1-datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120)) first_day_of_this_month
dateadd(month, -1, dateadd(day, 1-datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120))) first_day_of_prev_month
0
 
allanau20Author Commented:
thanks angelIII for your help.

The first sql gets the first day of the current month?

the second sql gets the first day of last month.
0
 
Mark WillsTopic AdvisorCommented:
or , using getdate() as the date source, can do :


select convert(datetime,convert(varchar(6),getdate() - day(getdate()),112)+'01') as start_of_prior_month ,dateadd(dd,-1,convert(varchar(6),getdate(),112)+'01') as end_of_prior_month

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Kevin CrossChief Technology OfficerCommented:
SELECT        
DATEADD(mm, - 1, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1)) AS FirstDayOfLastMonth
, DATEADD(mm, 0, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()))) AS LastDayOfLastMonth

This gets you both days at midnight.  if you are using this to include values from last month, I would use like this:

dateField >= DATEADD(mm, - 1, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1))
AND dateField < DATEADD(mm, 0, DATEDIFF(dd, 0, GETDATE() - DAY(GETDATE()) + 1))

Which goes from midnight of 10/1 inclusive to all dates less than 11/1.

If you just need the dates without the time, then you can use the convert Angel showed with this:

DATEADD(mm, - 1, GETDATE() - DAY(GETDATE()) + 1)  -- first day
DATEADD(mm, 0, GETDATE() - DAY(GETDATE()))  -- last day
0
 
Mark WillsTopic AdvisorCommented:
and if time is important (usually is in datetime),

select convert(datetime,convert(varchar(6),getdate() - day(getdate()),112)+'01 00:00:00') as start_of_prior_month, dateadd(dd,-1,convert(varchar(6),getdate(),112)+'01 23:59:59') as end_of_prior_month

0
 
allanau20Author Commented:
So many Experts so I'll try to be fair.

that's a good point mwvisa1 about  the time stamp, I was gonna solve that with DATEADD(dd,1,@EndDt)
0
 
allanau20Author Commented:
thank you all for your posts!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The first sql gets the first day of the current month?
so that's fine, right?

>the second sql gets the first day of last month.
usually, you want to do a date query for

where your_date_col >= first_day_of_previous_month
   and your_date_col < first_day_of_current_month

hence, my suggestion.

if you REALLY want to get the last day of the month date value:

select dateadd(day, 1-datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120)) first_day_of_this_month
, dateadd(month, -1, dateadd(day, 1-datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120))) first_day_of_prev_month
, dateadd(day, -datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120)) last_day_of_previous_month

Open in new window

0
 
allanau20Author Commented:
ara ara ... sorry .. already awarded the points...

Thank you for your posts.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now