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

• Thom A

  SSC Guru

  Points: 99129

  More actions

  January 27, 2017 at 4:41 am

  #1925279

  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

  January 27, 2017 at 4:53 am

  #1925280

  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: 99129

  More actions

  January 27, 2017 at 5:12 am

  #1925282

  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

  January 27, 2017 at 5:14 am

  #1925284

  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: 99129

  More actions

  January 27, 2017 at 5:15 am

  #1925285

  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

  January 27, 2017 at 5:18 am

  #1925286

  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: 99129

  More actions

  January 27, 2017 at 5:19 am

  #1925287

  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: 148809

  More actions

  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: 99129

  More actions

  January 27, 2017 at 5:23 am

  #1925289

  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

  January 27, 2017 at 5:32 am

  #1925291

  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: 42192

  More actions

  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: 1004312

  More actions

  January 31, 2017 at 9:41 pm

  #1925991

  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)
  

• Jeff Moden

  SSC Guru

  Points: 1004312

  More actions

  January 31, 2017 at 9:51 pm

  #1925992

  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)