Finding Within dates

Question

Post reply

Finding Within dates

Suth

SSCommitted

Points: 1559
More actions
January 27, 2017 at 4:31 am

#334535

Hi
Im new to sql and I have a date column created , I'm trying to find data within these time durations

If within 7 days
If between 7-14 days
if over 14 days

how would i go about writing this ?

Many thanks

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99009 More actions · Answer 1

DateColumn BETWEEN DATEADD(DAY-7, CAST(GETDATE() AS DATE)) AND GETDATE() --Would return rows where the DateColumn has a value 20-Jan-2017 to now INCLUSIVE



DateColumn BETWEEN DATEADD(DAY-14, CAST(GETDATE() AS DATE)) AND DATEADD(DAY-7, CAST(GETDATE()) AS DATE)) --Would return rows where the DateColumn has a value from 13-Jan-2017 to 20-Jan-2017 INCLUSIVE

DateColumn < DATEADD(DAY-14, CAST(GETDATE() AS DATE)) --Would rows where the DateColumn has a value before 13-Jan-2017

Note, there is an overlap on the first two. Depending on your requirements you can change the values.

I have also CAST GETDATE() as a date, as I have assumed that you want values from earlier in time 7 days ago. For example, it is 11:42 for me right now. If I did not cast GETDATE() to a date, the results that have a time prior to 11:42 on 20-Jan-2017 in the first statement would be excluded. That is because 20-Jan-2017 10:03 is 7 days, 1 hour and 39 minutes ago (which is more than 7 days ago).

If you column is a true DATE column, rather than DATETIME, then they would all be assumed to have a time value of 00:00:00.000 when compared to a DATETIME (which would have the same effect as above).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Suth SSCommitted Points: 1559 More actions · Answer 2

Thom A - Friday, January 27, 2017 4:41 AM

DateColumn BETWEEN DATEADD(DAY-7, CAST(GETDATE() AS DATE) AND GETDATE() --Would return rows where the DateColumn has a value 20-Jan-2017 to now INCLUSIVE DateColumn BETWEEN DATEADD(DAY-14, CAST(GETDATE() AS DATE) AND DATEADD(DAY-7, CAST(GETDATE() AS DATE) --Would return rows where the DateColumn has a value from 13-Jan-2017 to 20-Jan-2017 INCLUSIVE
DateColumn < DATEADD(DAY-14, CAST(GETDATE() AS DATE) --Would rows where the DateColumn has a value before 13-Jan-2017

Note, there is an overlap on the first two. Depending on your requirements you can change the values.

I have also CAST GETDATE() as a date, as I have assumed that you want values from earlier in time 7 days ago. For example, it is 11:42 for me right now. If I did not cast GETDATE() to a date, the results that have a time prior to 11:42 on 20-Jan-2017 in the first statement would be excluded. That is because 20-Jan-2017 10:03 is 7 days, 1 hour and 39 minutes ago (which is more than 7 days ago).

If you column is a true DATE column, rather than DATETIME, then they would all be assumed to have a time value of 00:00:00.000 when compared to a DATETIME (which would have the same effect as above).

how would i script this when im writing it within a case statement , so when created within 7 days when etc when etc end

ive tried to do the following



 SELECT 
        CASE 
            WHEN created BETWEEN DATEADD(DAY,-7, CAST(GETDATE() AS DATE) AND GETDATE() THEN 'Valid'
         
         END
FROM dbo.u_contact

but it doesn't like it

Thom A SSC Guru Points: 99009 More actions · Answer 3

CASE WHEN DateColumn BETWEEN DATEADD(DAY,-7, CAST(GETDATE() AS DATE)) AND GETDATE() THEN 1 WHEN DateColumn BETWEEN DATEADD(DAY,-14, CAST(GETDATE() AS DATE)) AND DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) THEN 2 WHEN DateColumn < DATEADD(DAY,-14, CAST(GETDATE() AS DATE)) THEN 3 END

To reiterate, the overlap is still present., however, a result of 7 days will always return 1 and never 2, as 1 is evaluated first. Also, if you have any values with a date value greater than the current date and time, NULL will be returned.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Suth SSCommitted Points: 1559 More actions · Answer 4

Thom A - Friday, January 27, 2017 5:12 AM

CASE WHEN DateColumn BETWEEN DATEADD(DAY-7, CAST(GETDATE() AS DATE) AND GETDATE() THEN 1 WHEN DateColumn BETWEEN DATEADD(DAY-14, CAST(GETDATE() AS DATE) AND DATEADD(DAY-7, CAST(GETDATE() AS DATE) THEN 2 WHEN DateColumn < DATEADD(DAY-14, CAST(GETDATE() AS DATE) THEN 3 END

To reiterate, the overlap is still present., however, a result of 7 days will always return 1 and never 2, as 1 is evaluated first. Also, if you have any values with a date value greater than the current date and time, NULL will be returned.

with that code i get Incorrect syntax near the keyword 'AND'.

Thom A SSC Guru Points: 99009 More actions · Answer 5

Suth - Friday, January 27, 2017 5:14 AM

Thom A - Friday, January 27, 2017 5:12 AM

CASE WHEN DateColumn BETWEEN DATEADD(DAY-7, CAST(GETDATE() AS DATE) AND GETDATE() THEN 1 WHEN DateColumn BETWEEN DATEADD(DAY-14, CAST(GETDATE() AS DATE) AND DATEADD(DAY-7, CAST(GETDATE() AS DATE) THEN 2 WHEN DateColumn < DATEADD(DAY-14, CAST(GETDATE() AS DATE) THEN 3 END

To reiterate, the overlap is still present., however, a result of 7 days will always return 1 and never 2, as 1 is evaluated first. Also, if you have any values with a date value greater than the current date and time, NULL will be returned.

with that code i get Incorrect syntax near the keyword 'AND'.

I've found copy and paste to be very unreliable on SSC recently, It seems to have stripped loads of my characters out.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Suth SSCommitted Points: 1559 More actions · Answer 6

Thom A - Friday, January 27, 2017 5:15 AM

Suth - Friday, January 27, 2017 5:14 AM

Thom A - Friday, January 27, 2017 5:12 AM

CASE WHEN DateColumn BETWEEN DATEADD(DAY-7, CAST(GETDATE() AS DATE) AND GETDATE() THEN 1 WHEN DateColumn BETWEEN DATEADD(DAY-14, CAST(GETDATE() AS DATE) AND DATEADD(DAY-7, CAST(GETDATE() AS DATE) THEN 2 WHEN DateColumn < DATEADD(DAY-14, CAST(GETDATE() AS DATE) THEN 3 END

To reiterate, the overlap is still present., however, a result of 7 days will always return 1 and never 2, as 1 is evaluated first. Also, if you have any values with a date value greater than the current date and time, NULL will be returned.

with that code i get Incorrect syntax near the keyword 'AND'.

I've found copy and paste to be very unreliable on SSC recently, it stripped by double brackets. I've corrected my post since.

Still the same issue appears



SELECT 
CASE WHEN Created BETWEEN DATEADD(DAY,-7, CAST(GETDATE() AS DATE) AND GETDATE() THEN 1
   WHEN Created BETWEEN DATEADD(DAY,-14, CAST(GETDATE() AS DATE) AND DATEADD(DAY-7, CAST(GETDATE() AS DATE) THEN 2
   WHEN Created < DATEADD(DAY,-14, CAST(GETDATE() AS DATE) THEN 3
END
FROM dbo.u_contact

Thom A SSC Guru Points: 99009 More actions · Answer 7

CREATE TABLE #Date (DateColumn DATE) GO



INSERT INTO #Date 
SELECT '01-Jan-2017';
GO

SELECT CASE WHEN DateColumn BETWEEN DATEADD(DAY,-7, CAST(GETDATE() AS DATE)) AND GETDATE() THEN 1
    WHEN DateColumn BETWEEN DATEADD(DAY,-14, CAST(GETDATE() AS DATE)) AND DATEADD(DAY,-7, CAST(GETDATE() AS DATE)) THEN 2
    WHEN DateColumn < DATEADD(DAY,-14, CAST(GETDATE() AS DATE)) THEN 3
   END
FROM #Date
GO

DROP TABLE #Date;

It works.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 8

John Mitchell-245523

SSC Guru

Points: 148786

January 27, 2017 at 5:22 am

#1925288

Come on, this is a simple syntax error. You should be able to troubleshoot it yourself. Hint: count the open parentheses and the close parentheses on each line.

John

Thom A SSC Guru Points: 99009 More actions · Answer 9

John Mitchell-245523 - Friday, January 27, 2017 5:22 AM

Come on, this is a simple syntax error. You should be able to troubleshoot it yourself. Hint: count the open parentheses and the close parentheses on each line.

John

I want to know where my parenthesis went when I pasted 🙁

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Suth SSCommitted Points: 1559 More actions · Answer 10

Thom A - Friday, January 27, 2017 5:23 AM

John Mitchell-245523 - Friday, January 27, 2017 5:22 AM

Come on, this is a simple syntax error. You should be able to troubleshoot it yourself. Hint: count the open parentheses and the close parentheses on each line.

John

I want to know where my parenthesis went when I pasted 🙁

Works fine now it was the brackets after the

AS DATE))

was missing.

Thank you for your help

Chris Harshman SSC-Forever Points: 42179 More actions · Answer 11

Chris Harshman

SSC-Forever

Points: 42179

January 27, 2017 at 7:25 am

#1925324

I've been having copy and paste issues too in the new forums... I've been pasting my queries into Notepad, then copying from that to paste into the forums, seems to work better for me anyway. 😛

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Thom A - Friday, January 27, 2017 5:19 AM

CREATE TABLE #Date (DateColumn DATE) GO INSERT INTO #Date SELECT '01-Jan-2017'; GO SELECT CASE WHEN DateColumn BETWEEN DATEADD(DAY,-7, CAST(GETDATE() AS DATE)) AND GETDATE() THEN 1 WHEN DateColumn BETWEEN DATEADD(DAY,-14, CAST(GETDATE() AS DATE)) AND DATEADD(DAY,-7, CAST(GETDATE() AS DATE)) THEN 2 WHEN DateColumn < DATEADD(DAY,-14, CAST(GETDATE() AS DATE)) THEN 3 END FROM #Date GO
DROP TABLE #Date;

It works.

You can cheat like hell on this one, Tom. Remember that none of it will be greater than GETDATE() and remember that the CASE statement evaluates in the same order as what it is written. That will allow you to greatly simplify the code especially if you think about it "backwards". :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Suth - Friday, January 27, 2017 4:31 AM

Hi
Im new to sql and I have a date column created , I'm trying to find data within these time durations

If within 7 days
If between 7-14 days
if over 14 days

how would i go about writing this ?

Many thanks

What format should the output look like?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions