Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sql query  last six months

Posted on 2013-06-21
5
Medium Priority
?
1,995 Views
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
0
Comment
Question by:Angela4eva
  • 3
5 Comments
 
LVL 15

Assisted Solution

by:gplana
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:

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

For last 6 months:

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

Accepted Solution

by:
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.
SELECT *
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 (?)
select
  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")

SELECT
  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
select
  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:
select
  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
select
  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
http://sqlfiddle.com/#!3/1fa93/7883

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

Expert Comment

by:PortletPaul
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
0
 

Author Comment

by:Angela4eva
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
---yes
0
 
LVL 49

Expert Comment

by:PortletPaul
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)
    )
select
id, datename(weekday,StartAt), StartAt
from ranger

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…

577 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