CODEWITHSUNDEEP
sqldatetimebetween
Withdalot
Withdalot

Reputation: 109

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

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.

Upvotes: 2

Views: 29158

Answers (5)

Vidu VDS
Vidu VDS

Reputation: 72

I think you can use these tips :

SET @start = CURDATE() - interval 1 DAY + interval 2 HOUR;
SET @end = CURDATE() + interval 2 HOUR;

And :

SELECT * FROM TABLE_NAME WHERE DATE_FIELD BETWEEN @start AND @end

Upvotes: 0

Piyush Ssharma
Piyush Ssharma

Reputation: 1

This can also help you out

where pt.status in (1) 
    and pt.is_visible in ('t') 
    and pt.merchant in (0) 
    and (date(pt.created_at+'05:30') >= current_date-2 
            and extract(hour from(pt.created_at+'05:30')) >=17)
    and ((pt.created_at+'05:30') <= current_date-1
            and extract(hour from(pt.created_at+'05:30')) <=17)

Upvotes: 0

venkat
venkat

Reputation: 11

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'

Upvotes: 1

WhatAWorld
WhatAWorld

Reputation: 394

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.

Upvotes: 1

James Hill
James Hill

Reputation: 61792

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'

Upvotes: 6

Related Questions