Yong Xiang Soh Yong Xiang Soh - 20 days ago 7
SQL Question

Select records between yesterday and today

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 it returns me 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

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.