Solved

# SQL 5 Day Date Query on a specific date every month

Posted on 2015-02-09
185 Views
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
Question by:PeelSeel2

LVL 65

Assisted Solution

Jim Horn earned 250 total points
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)
``````
0

LVL 32

Accepted Solution

Stefan Hoffmann earned 250 total points
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());
``````

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

Thanks!  Both of these work well.
0

LVL 32

Expert Comment

hmm, as its SQL 2012, Jim should have used DATEFROMPARTS() instead of a casting show :)
0

LVL 65

Expert Comment

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

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

``````|   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 |
``````
``````|   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 |
``````

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
;
``````

``````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
;
``````

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' )
;

;
``````
0

LVL 32

Expert Comment

@Paul is right about the time portion. My fault.
0

## Featured Post

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.