Solved

SQL 5 Day Date Query on a specific date every month

Posted on 2015-02-09
7
185 Views
Last Modified: 2015-02-10
I have a table with the following example data:

object,value,timestamp
75721852,21620,2/1/15 3:02
75721852,22451,2/1/15 5:30
75721852,21621,2/2/15 3:07
75721852,22452,2/2/15 5:33
75721852,21621,2/3/15 3:07
75721852,22453,2/3/15 5:30
75721852,21622,2/4/15 3:04
75721852,22454,2/4/15 5:31
75721852,21623,2/5/15 3:00
75721852,22455,2/5/15 5:30
75721852,21623,2/6/15 3:00
75721852,22455,2/6/15 5:32
75721852,21624,2/7/15 3:04
75721852,22456,2/7/15 5:32
75721852,21624,2/8/15 3:04
75721852,22457,2/8/15 5:30
75721852,21625,2/9/15 3:09
75721852,22458,2/9/15 5:30

I need to query the timestamp field for a specific time of the month.  Any time I run it, I want it to query from the 8th of the current month back 5 days.
0
Comment
Question by:PeelSeel2
7 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 total points
Comment Utility
I'm sure there's a more elegant way to pull this off, but this should work..
Also, define for us further '8th of the month and back five days.   Does that mean the 3rd of the month beginning at midnight, running to the 8th of the month beginning at midnight?  
SELECT *
FROM YourTable
WHERE Timestamp >= CAST(CAST(YEAR(Getdate()) as char(4)) + '-' + RIGHT('0' + CAST(MONTH(GETDATE()) as varchar(2)), 2) + '-03' as date) AND 
Timestamp < CAST(CAST(YEAR(Getdate()) as char(4)) + '-' + RIGHT('0' + CAST(MONTH(GETDATE()) as varchar(2)), 2) + '-08' as date) 

Open in new window

0
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 250 total points
Comment Utility
Hmm, when I understand you correctly:

 
DECLARE @Sample TABLE
    (
      [object] INT ,
      value INT ,
      [timestamp] DATETIME
    );
;

INSERT  INTO @Sample
VALUES  ( 75721852, 21620, '2/1/15 3:02' ),
        ( 75721852, 22451, '2/1/15 5:30' ),
        ( 75721852, 21621, '2/2/15 3:07' ),
        ( 75721852, 22452, '2/2/15 5:33' ),
        ( 75721852, 21621, '2/3/15 3:07' ),
        ( 75721852, 22453, '2/3/15 5:30' ),
        ( 75721852, 21622, '2/4/15 3:04' ),
        ( 75721852, 22454, '2/4/15 5:31' ),
        ( 75721852, 21623, '2/5/15 3:00' ),
        ( 75721852, 22455, '2/5/15 5:30' ),
        ( 75721852, 21623, '2/6/15 3:00' ),
        ( 75721852, 22455, '2/6/15 5:32' ),
        ( 75721852, 21624, '2/7/15 3:04' ),
        ( 75721852, 22456, '2/7/15 5:32' ),
        ( 75721852, 21624, '2/8/15 3:04' ),
        ( 75721852, 22457, '2/8/15 5:30' ),
        ( 75721852, 21625, '2/9/15 3:09' ),
        ( 75721852, 22458, '2/9/15 5:30' ),
        ( 75721852, 22458, '5/2/15 5:30' );

SELECT  *
FROM    @Sample S
WHERE   S.[timestamp] BETWEEN DATEADD(DAY, -DAY(GETDATE()) + 3, GETDATE()) AND DATEADD(DAY, -DAY(GETDATE()) + 8, GETDATE());

Open in new window


I'm have gotten the interval not correctly, but this depends what you really want. cause from the 8th back 5 is always 3rd to 7th day in my understanding. So maybe you need to adjust that.
0
 

Author Closing Comment

by:PeelSeel2
Comment Utility
Thanks!  Both of these work well.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
hmm, as its SQL 2012, Jim should have used DATEFROMPARTS() instead of a casting show :)
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Mercy.  My last exam I took was 2008, and we were on 2008 at my current client until just recently, so I'm displaying some lingering effects from that world.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I think you need to double check the time of day if using GETDATE() and merely deducting/adding days

getdate() returns both date and time, if you deduct 3 days the time portion is unchanged

Please compare these 2 results:
|   OBJECT | VALUE |                       TIMESTAMP |                        COLUMN_3 |                        COLUMN_4 |
|----------|-------|---------------------------------|---------------------------------|---------------------------------|
| 75721852 | 21622 | February, 04 2015 03:04:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 22454 | February, 04 2015 05:31:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 21623 | February, 05 2015 03:00:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 22455 | February, 05 2015 05:30:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 21623 | February, 06 2015 03:00:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 22455 | February, 06 2015 05:32:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 21624 | February, 07 2015 03:04:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 22456 | February, 07 2015 05:32:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 21624 | February, 08 2015 03:04:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |
| 75721852 | 22457 | February, 08 2015 05:30:00+0000 | February, 03 2015 05:44:30+0000 | February, 08 2015 05:44:30+0000 |

Open in new window

|   OBJECT | VALUE |                       TIMESTAMP |   COLUMN_3 |   COLUMN_4 |
|----------|-------|---------------------------------|------------|------------|
| 75721852 | 21621 | February, 03 2015 03:07:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 22453 | February, 03 2015 05:30:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 21622 | February, 04 2015 03:04:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 22454 | February, 04 2015 05:31:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 21623 | February, 05 2015 03:00:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 22455 | February, 05 2015 05:30:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 21623 | February, 06 2015 03:00:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 22455 | February, 06 2015 05:32:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 21624 | February, 07 2015 03:04:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 22456 | February, 07 2015 05:32:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 21624 | February, 08 2015 03:04:00+0000 | 2015-02-03 | 2015-02-09 |
| 75721852 | 22457 | February, 08 2015 05:30:00+0000 | 2015-02-03 | 2015-02-09 |

Open in new window


produced by these 2 queries (respectively)
SELECT  *
        , DATEADD(DAY, -DAY(GETDATE()) + 3, GETDATE())
        , DATEADD(DAY, -DAY(GETDATE()) + 8, GETDATE())
FROM    Sample S
WHERE   S.[timestamp] BETWEEN DATEADD(DAY, -DAY(GETDATE()) + 3, GETDATE()) 
                          AND DATEADD(DAY, -DAY(GETDATE()) + 8, GETDATE())

order by timestamp
;

Open in new window


SELECT  *
        , DATEADD(DAY, -DAY(GETDATE()) + 3, cast(GETDATE() as date))
        , DATEADD(DAY, -DAY(GETDATE()) + 9, cast(GETDATE() as date))
FROM    Sample S
WHERE   S.[timestamp] >= DATEADD(DAY, -DAY(GETDATE()) + 3, cast(GETDATE() as date)) 
    AND S.[timestamp] <  DATEADD(DAY, -DAY(GETDATE()) + 9, cast(GETDATE() as date))

order by timestamp
;

Open in new window


I'm not sure if you were expecting the time of day (other than 00:00:00)

note also that I never use between for date ranges.
see: "Beware of Between"

ddl used
create table Sample
    (
      [object] INT ,
      value INT ,
      [timestamp] DATETIME
    );
;

INSERT  INTO Sample
VALUES  ( 75721852, 21620, '2/1/15 3:02' ),
        ( 75721852, 22451, '2/1/15 5:30' ),
        ( 75721852, 21621, '2/2/15 3:07' ),
        ( 75721852, 22452, '2/2/15 5:33' ),
        ( 75721852, 21621, '2/3/15 3:07' ),
        ( 75721852, 22453, '2/3/15 5:30' ),
        ( 75721852, 21622, '2/4/15 3:04' ),
        ( 75721852, 22454, '2/4/15 5:31' ),
        ( 75721852, 21623, '2/5/15 3:00' ),
        ( 75721852, 22455, '2/5/15 5:30' ),
        ( 75721852, 21623, '2/6/15 3:00' ),
        ( 75721852, 22455, '2/6/15 5:32' ),
        ( 75721852, 21624, '2/7/15 3:04' ),
        ( 75721852, 22456, '2/7/15 5:32' ),
        ( 75721852, 21624, '2/8/15 3:04' ),
        ( 75721852, 22457, '2/8/15 5:30' ),
        ( 75721852, 21625, '2/9/15 3:09' ),
        ( 75721852, 22458, '2/9/15 5:30' ),
        ( 75721852, 22458, '5/2/15 5:30' )
;

;

Open in new window

0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
@Paul is right about the time portion. My fault.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
Resolve DNS query failed errors for Exchange
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now