H2 - DATEADD for a full day

Question

this query will retrieve all records for the last 7 days.

SELECT * FROM STATISTICS WHERE TIMESTAMP > DATEADD('DAY',-7, NOW()) 

How can I modify the request to include recordings from midnight 7 days ago?

ex.

 SELECT DATEADD('DAY',-7, NOW()) 

Gives: 2013-09-25 13: 46: 54.372

But I would like: 2013-09-25 00: 00: 00.000

Answer 1

For everyone who is interested, here is a solution.

Using CURRENT_DATE instead of NOW () does the trick!

Example:

 SELECT * FROM STATISTICS WHERE TIMESTAMP > DATEADD('DAY',-7, CURRENT_DATE) 

http://www.h2database.com/html/functions.html#dateadd

Answer 2

in SQL Server you can do this:

 SELECT DATEADD(DAY, -7, DATEDIFF(DAY, 0, GETDATE())) 

Perhaps you can configure this to work in H2.