DATEADD Function

• PSB

  SSChampion

  Points: 13195

  More actions

  January 28, 2011 at 11:41 am

  #231171

  Hi,

  I have a date field named MyDate datatype datetime

  e.g =2010-06-23 00:00:00.000

  in my query I want to get results since the last 24 hours.

  I.e From 1/28/2011 to 1/29/2011

  When I hard code the date as mentioned above in the query below i get results

  SELECt * from mytable name

  WHERE MyDate ='1/28/2011

  AND MyDate <= '1/29/2011'

  But when I use the query below, no data is returned.

  SELECt * from mytable name

  WHERE MyDate=DATEADD(dd, DATEDIFF(dd, GETDATE(),GETDATE()-1), GETDATE())

  AND MyDate <=DATEADD(dd, DATEDIFF(dd, GETDATE(),GETDATE()+1), GETDATE())

  Please help!

• Mike John

  SSCertifiable

  Points: 7216

  More actions

  January 28, 2011 at 11:49 am

  #1279864

  I think the first line of the where clause should probably be using >= rather than = as the conditions as posted are mutually exclusive...

  Mike

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  January 28, 2011 at 11:52 am

  #1279866

  select

  *

  from

  MyTable

  where

  --Equal to or greater than start of yesterday

  MyDate >= dateadd(dd, datediff(dd, 0,getdate())-1,0)and

  --Less than start of yesterday

  MyDate < dateadd(dd, datediff(dd, 0,getdate()),0)

• PSB

  SSChampion

  Points: 13195

  More actions

  January 28, 2011 at 12:05 pm

  #1279872

  Mike : It was a typo. I actually meant that.

  Michael: Thanks.It worked!