Previous Day Query on Date time field 1

[Freemo]

I am trying to run a report to find transactions created the previous day. I am using the following criteria:
where createddate = CONVERT(CHAR(11),getdate()-1)
Created date is a date time field and when i run this query it only returns transactions processed dead on midnight.

Any ideas how i can get it to display all transactions for the previous day?

Many thanks

 

[bborissov]

SELECT * 
       FROM YourTable
WHERE DateTimeField >= DateAdd(dd,DateDiff(dd,0,GETDATE()),-1) AND
      DateTimeField < DateAdd(dd,DateDiff(dd,0,GETDATE()),0)

(not tested)

DateAdd(dd,DateDiff(dd,0,GETDATE()),-1) will give you datetime value as midnight of the previous date i.e: '02/27/2008 00:00:00'

DateAdd(dd,DateDiff(dd,0,GETDATE()),0) will give you datetime value as midnight of the current date i.e: '02/28/2008 00:00:00'

That is why first check is for bigger or equal and second check is for smaller only.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP