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?

Answer 1

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'}