CODEWITHSUNDEEP
sqldatems-accesstimefilter
Priyanka Kochar
Priyanka Kochar

Reputation: 1

Filter on date and time in MS Access query with datetime values

For example i want to retrieve everything in the database between day before yesterday 10 PM to Yesterday 10 PM. I have it in this format as of now (7/2/2020 2:00:00 PM).

Upvotes: 0

Views: 1403

Answers (3)

Gustav
Gustav

Reputation: 55831

Count the differences in hours and use DateAdd:

Select *
From YourTable
Where YourDateField Between
    DateAdd("h", -26, Date()) And DateAdd("h", -2, Date())

Upvotes: 0

Parfait
Parfait

Reputation: 107587

Simply add date and time elements using Date() (which begins time at midnight, '00:00:00') and CDate (notice time format can differ for same values, e.g., 10 PM vs 22:00).

SELECT ...
FROM myTable
WHERE myDate BETWEEN (Date() - 2) + CDate('10 PM')
                 AND (Date() - 1) + CDate('22:00')

Upvotes: 1

Ekta Gupta
Ekta Gupta

Reputation: 19

Assuming table1 as table and col1 as column

declare @yesterday datetime
SELECT @yesterday=DATEADD(millisecond,DATEDIFF(millisecond, 0, (SELECT 
                  CONVERT(TIME,'22:00:00.000')) ),CAST( (convert(date, 
                  dateadd(day,datediff(day,1, GETDATE()),0)))  AS DATETIME));

declare @daybeforeyesterday datetime
SELECT @daybeforeyesterday=DATEADD(millisecond
                           ,DATEDIFF(millisecond, 0, (SELECT CONVERT(TIME,'22:00:00.000')) 
                           ),CAST( (convert(date, dateadd(day,datediff(day,2, 
                           GETDATE()),0)))  AS DATETIME));

select * from table1 where col1 between @yesterday and @daybeforeyesterday

Upvotes: 0

Related Questions