If you cast the result as date you'll only get a date with the time set to 00:00. Try using a datettime.
The three criteria I think you need (based on your first post) are
Code:
The first time will be around 3 AM and will run for yesterday - All records for yesterday = YourDateField>= DATEADD(DAY, DATEDIFF(day, 0, getdate()), -1) AND YourDateField < DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
The second will be around noon and will run for today - All records for today so far = YourDateField >= DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
the third will be around 6 PM and will also run for today. Ditto above = YourDateField >= DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)