Scott Abraham
asked on
SQL 5 Day Date Query on a specific date every month
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hmm, as its SQL 2012, Jim should have used DATEFROMPARTS() instead of a casting show :)
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.
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:
produced by these 2 queries (respectively)
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
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 |
| 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' )
;
;
@Paul is right about the time portion. My fault.
ASKER