sql query  last six months

Posted on 2013-06-21
Medium Priority
Last Modified: 2013-07-25
I need help writing a  query
 where cdate
is last 6 Mondays
last 4 days
first of last 3 months
Question by:Angela4eva
  • 3
LVL 15

Assisted Solution

gplana earned 600 total points
ID: 39267046
I think you need to have a look at this link: http://msdn.microsoft.com/en-us/library/aa258863%28v=sql.80%29.aspx

For the last 4 days:

FROM your_table
WHERE date >= dateadd(Day,-4,GETDATE());

For last 6 months:

FROM your_table
WHERE date >= dateadd(Month,-6,GETDATE());
LVL 49

Accepted Solution

PortletPaul earned 1400 total points
ID: 39267532
all of the examples below can be seen working at http://sqlfiddle.com/#!3/1fa93/7883

the following may bend your head a bit, working with date/time can be confusing.

first: getdate() supplies the current date AND time

==== removing time
So when you ask for "last 4 days", what most people mean is "the last 4 COMPLETE days", so to meet "complete" you must remove the time from getdate()

in sql server 2008+ you can use this to remove time
select cast(getdate() as date) -- or
select convert(date, getdate() )
or while the following may look more complex - it is super efficient and works in all sql server versions:
select dateadd(day, datediff(day,0, getdate() ), 0)

==== last 4 complete days e.g.
FROM your_table
WHERE cdate >= dateadd(Day,-4, cast(getdate() as date)  );

===== first of last 3 months

I'm not entirely sure what you mean here, is it "the first day of the month, 3 months before now"?
-- first of last 3 months (?)
  dateadd(month,-3,cast(getdate() as date))
      as [3 months ago]
, datepart(day,dateadd(month,-3,getdate()))
      as [day of month 3 months ago]
, dateadd(day,-datepart(day,dateadd(month,-3,getdate()))+1, dateadd(month,-3,cast(getdate() as date)) )
      as [first of month 3 months ago]

-- first of last 3 months (? a "quarter")

  dateadd(quarter,datediff(quarter,0,getdate()),0)    as [First of this quarter]
, dateadd(quarter,datediff(quarter,0,getdate())+1,-1) as [Last day of this quarter]

Open in new window

==== last 6 months
  dateadd(month,-6, cast(getdate() as date) ) as [last 6 months by cast]
, dateadd(month,-6, dateadd(day, datediff(day,0, getdate() ), 0) ) as [last 6 months by dateadd/diff]
===== (first of) last 6 months
same as the 3 months above, just change the deductions (could use an @variable instead)

, dateadd(day,-datepart(day,dateadd(month,-6,getdate()))+1, dateadd(month,-6,cast(getdate() as date)) )
      as [first of month 6 months ago]
====== is last 6 Mondays

This is harder, and needs more information. If you mean, EACH OF the last 6 mondays this may require 6 records in a table or CTE

if you mean, Monday of the week, 6 weeks ago:
  dateadd(DAY, datediff(DAY,-7, getdate() ) - (6*7) - (datediff(DAY,0, getdate() ) % 7), 0)
     as [Monday of 6 weeks ago]

This last one is harder to explain.
-- to explain the above
  datediff(DAY,-7, getdate() ) as [num days to 7days ago]
, -(6*7) as [6 week deduction in days]
, - (datediff(DAY,0, getdate() ) % 7) [if not monday also deduct this num of days]
,   dateadd(DAY, datediff(DAY,-7, getdate() ) - (6*7) - (datediff(DAY,0, getdate() ) % 7), 0)
     as [Monday of 6 weeks ago]

-- as I said, it can bend the mind :)

please do look at the sqlfiddle, seeing is believing

nb: if "is last 6 Mondays" means each 6 dates, this can be done also e.g. this answer
LVL 49

Expert Comment

ID: 39277329
Hi, is there any more you need on this question?
sql query last six months

I need help writing a  query
 where cdate
is last 6 Mondays
last 4 days
first of last 3 months

Author Comment

ID: 39283226
Thankd for the help. Yes I need mondays of last six weeks
 If you mean, EACH OF the last 6 mondays this may require 6 records in a table or CTE
LVL 49

Expert Comment

ID: 39283272
see this working at http://sqlfiddle.com/#!3/1fa93/7960 
-- set number of weeks
declare @numWeeks as int
set @numWeeks = 6

-- next get recent Monday
declare @BeginsAt as datetime

-- truncate time from getdate()
set @BeginsAt = dateadd(day, datediff(day,0, getdate() ), 0)

-- get Monday using modulus
set @BeginsAt = dateadd(day,(-datediff(day,0,@BeginsAt) % 7),@BeginsAt) 

;with Ranger (id, StartAt)
as (
    /* recursively build CTE of Mondays */
    select 1 as id, @BeginsAt
    union all
    select (id + 1) , dateadd(day,-7,StartAt)
    from Ranger
    where dateadd(day,-7,StartAt) >= dateadd(week,-@numWeeks+1,@BeginsAt)
id, datename(weekday,StartAt), StartAt
from ranger

Open in new window


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

627 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