Select records between yesterday and today

Question

I have trying to implement a simple SQL query that gets records between today's fixed timing (e.g. 18:00) and yesterday's fixed timing (e.g. 18:00).

SELECT * FROM [Table]
WHERE [Table].[Date Time] > Now()-1;

However, this will return all records between today's current timing and yesterday's.

Is there a way to return records within specified timings?

Edit: I tried this query, but its throwing syntax error.

SELECT * FROM [Table]
WHERE [Date Time] BETWEEN FORMAT(DATEADD(DAY, - 1, NOW()) AS DATETIME) + FORMAT('18:00:00' AS TIME) AND FORMAT(NOW() AS DATETIME) + FORMAT('18:00:00' AS TIME);

Any help?

Solution 1

You need to add the variable date values and the fixed time value.

Date/Time constants can be fickle with all the different local formats. With TimeSerial we can avoid this:

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] Between Date()-1 + TimeSerial(18,0,0) 
                              And Date()   + TimeSerial(18,0,0)

---

(First suggestion, works with German date format)

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] Between Date()-1 + #18:00:00# And Date() + #18:00:00#

The query designer may automatically change this into

Between Date()-1 + #12/30/1899 18:00:00# And Date() + #12/30/1899 18:00:00#

1899-12-30 is "Date zero" in Access.

Solution 2

You mean something like this?

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] between #16/11/2016 14:00:00# and #17/11/2016 14:00:00#

but pay attention to MDY - DMY date format..

I prefer ODBCC format to avoid problems:

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] between {ts'1970-01-12 14:00:00' } and {ts'1975-01-12 14:00:00'}