CODEWITHSUNDEEP
sqlms-accessms-access-2016
Yong Xiang Soh
Yong Xiang Soh

Reputation: 97

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 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?

Upvotes: 2

Views: 20864

Answers (6)

Andre
Andre

Reputation: 27634

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.

Upvotes: 1

MtwStark
MtwStark

Reputation: 4048

use date() instead of now()

suppose today is 17nov (any time),

if you need records of yesterday 16nov (00:00 -> 23:59) you must use date()-1 as lower bound and date() as upper bound 

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] between date()-1 and date()

if you need records of today 17nov (00:00 -> 23:59) you must use date() as lower bound and date()+1 as upper bound

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] between date() and date()+1

this is the simple version and it has a little problem with boundaries, the correct filter should be (for yesterday)

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] >= date()-1 [Table].[Date Time] < date()

or, for today:

SELECT * 
FROM [Table]
WHERE [Table].[Date Time] >= date() [Table].[Date Time] < date()+1

Upvotes: 0

grom
grom

Reputation: 61

You probably want to first declare a variable for todays date at 18.00 and then compare your date to that, in Access you can make use of the TimeValue() function:

Dim Time18
Time18 = TimeValue("18:00")

Then you can compare your date to that and one day back:

SELECT * FROM [Table]
WHERE (
    (DateValue([Table].[Date Time]) = DateValue(Now() - 1) AND 
     TimeValue([Table].[Date Time]) >= Time18) OR
    (DateValue([Table].[Date Time]) = DateValue(Now() AND 
     TimeValue([Table].[Date Time]) <= Time18))

Upvotes: 0

Alankrit Agrawal
Alankrit Agrawal

Reputation: 109

You can try following query for SQL Server:

SELECT * FROM [Table]
WHERE DATEADD(HH, 18, CAST(CAST([Table].[Date Time] AS DATE) AS DATETIME)) > DATEADD(HH, 18, CAST(CAST(Now() AS DATE) AS DATETIME))-1;

Upvotes: 0

MtwStark
MtwStark

Reputation: 4048

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

Upvotes: 2

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Try below query

            declare
                    @StartDate DATE = NULL,
                    @EndDate DATE = NULL

            SELECT * FROM [Table]       
             WHERE
             CAST( [Date Time] AS DATE) >= CAST( ISNULL(@StartDate, '') AS DATE) 
             AND CAST( [Date Time] AS DATE) <= CAST( ISNULL(@EndDate, '') AS DATE)

or simply put date into @StartDate and @EndDate.

Upvotes: 0

Related Questions