How can I find the data between two specific times in SQL

sql date time between

25,994

Solution 1

Based on the information that you provided, I can only give a generic example:

SELECT    *
FROM      [YourTable]
WHERE     [YourDate] BETWEEN '08-15-2011 02:00:00' AND '08-16-2011 02:00:00'

**EDIT**

Per a good suggestion from the comments, here is a reusable version:

SELECT    *
FROM      [YourTable]
WHERE     [YourDate] BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '02:00'
                     AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '02:00'

Solution 2

Assuming mysql:

SELECT * FROM your_table 
WHERE STR_TO_DATE(your_date, '%m-%d-%Y %H.%i.%s') BETWEEN
DATE_SUB(STR_TO_DATE(DATE_FORMAT(NOW(), '%m-%d-%Y'), '%m-%d-%Y'), INTERVAL 22 HOUR) AND
DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%m-%d-%Y'), '%m-%d-%Y'), INTERVAL 2 HOUR)

I'm sure there's a better way though.

Solution 3

The query doesn't return any records if we use as follows...

SELECT    *
FROM      [YourTable]
WHERE     [YourDate] BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '02:00'
                 AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '02:00'

We are using between clause so the oldest date should be first and the query becomes as follows...

SELECT    *
FROM      [YourTable]
WHERE     [YourDate] BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '02:00'
                 AND DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '02:00'

25,994

Author by

Withdalot

Updated on May 03, 2020

Comments

Withdalot about 4 years

I need to run a query that finds all login dates (dd/mm/yyyy) and times (hh.mm) that occurred between 2am yesterday and 2am today. The login time in the database is formatted as mm-dd-yyyy hh.mm.ss.

I have tried multiple select and between queries that either return data on the wrong day or outside the time spans.
Vinay almost 13 years

What if I have to get a time between other dates and time?
Vinay almost 13 years

I am sorry, its not my question and I did not downvote. I hardly downvote to any question. But my point is yesterday and today can be different dates. What if I have to run this query tomorrow?
James Hill almost 13 years

@Vinay, sorry, I jumped the gun and I apologize. You make a good point. I updated my answer.
Withdalot almost 13 years

This is the sort of thing I need, however the specific dates wont exist as the query needs to run daily looking for the record between 2am 'yesterday' and 2am 'today'...
James Hill almost 13 years

@Withdalot, the SQL under the EDIT section will fulfill that requirement.
Withdalot almost 13 years

@Vinay - Thats my exact issue. Thanks for assisting to clarify.
Withdalot almost 13 years

@James - sorry James I still have no idea how this would work
James Hill almost 13 years

@Withdalot, Did you try it? DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) + '02:00' will return the current date at 2 AM. DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()-1), 0) + '02:00' will return yesterdays date at 2 AM. Please give it a try.
Withdalot almost 13 years

James this is great and it works a treat. Thanks so much for your help... Cheers
James Hill almost 13 years

You're welcome. Don't forget to accept the answer: meta.stackexchange.com/questions/5234/…. @Vinay, thanks again for clarifying the need.