Help with date function

• Vik Nat

  Hall of Fame

  Points: 3930

  More actions

  December 9, 2003 at 5:29 pm

  #101797

  I have a table that has x number of records. I need (for reporting purposes) a query that will return rows based on a date range - say the table is called requests and the date column is called request_date_time. I need a seperate queries that will return a set of rows based on these criteria:

  1. past 24 hrs( simple enough)

  2. For the current month from the 1st till today.

  3. For the current year till today( from the 1st of Jan till today).

  4. For the current week - From Sunday till today.

  Help is appreciated.

• philcart

  SSC-Forever

  Points: 47796

  More actions

  December 9, 2003 at 6:33 pm

  #485449

  Try these queries,

  
  
  -- 1. past 24 hrs( simple enough)
  
  -- use seconds to take into account partial hours
  
  SELECT * FROM requests WHERE DATEDIFF(ss, request_date_time, GetDate()) < 86400
  
  
  
  -- 4. For the current week - From Sunday till today.
  
  -- by default Sunday is the first day of a week.
  
  SELECT * FROM requests WHERE DATEDIFF(wk, request_date_time, GetDate()) = 0
  
  
  
  -- 2. For the current month from the 1st till today.
  
  SELECT * FROM requests WHERE DATEDIFF(mm, request_date_time, GetDate()) = 0
  
  
  
  -- 3. For the current year till today( from the 1st of Jan till today).
  
  SELECT * FROM requests WHERE DATEDIFF(yy, request_date_time, GetDate()) = 0
  
  

  Hope this helps

  Phill Carter

  --------------------

  Colt 45 - the original point and click interface

  --------------------
  Colt 45 - the original point and click interface

• Vik Nat

  Hall of Fame

  Points: 3930

  More actions

  December 9, 2003 at 8:36 pm

  #485457

  Thanks Phill, thats what I needed. Appreciate the Quick response.