Select DateTime field based on today 1

[Bunting]

I need a query to select records using a DateTime field based on today's date without worrying about the current time.

I currently have

WHERE "Activity_Log"."Log_Date" BETWEEN DATEADD(DAY,-7,GETDATE()) AND DATEADD(DAY,-1,GETDATE())

My problem is that I want to select full days, so if I run this at 11am on a Monday I want to bring back all records from the previous Monday, whereas this will bring back only those after 11am.

Essentially I want to change DATEADD(DAY,-7,GETDATE()) to represent midnight on the selected date, similarly for the end date.

Many thanks for your help.

 

[r937]

faq183-5842 #7

r937.com | rudy.ca

 

[Bunting]

Thanks for that, I think I can see where to alter my query but I still get an error message. I've changed my code to:

WHERE  "Activity_Log"."Log_Date" IN CONVERT(SMALLDATETIME, CONVERT(VARCHAR(8), DATEADD(DAY,-7,GETDATE()), 112)) TO CONVERT(SMALLDATETIME, CONVERT(VARCHAR(8), DATEADD(DAY,-1,GETDATE()), 112))

This now gives me the error message 'Incorrect syntax near the word CONVERT'. Is there something obvious I have overlooked?

 

[r937]

where Activity_Log.Log_Date 
 between DATEADD(d,DATEDIFF(d,0,GETDATE()),-7)
     and DATEADD(d,DATEDIFF(d,0,GETDATE()),-1)

r937.com | rudy.ca

 

[Bunting]

Excellent, that's paerfect. Thank you for your help.