CODEWITHSUNDEEP
sql-serversql-server-2008
Grinex
Grinex

Reputation: 128

SQL select between two dates and time

I need to select all data from yesterday of 6:59 PM up to 7:00 PM of today everyday.

Data to be selected is a datetime field. The date is not constant as I am going to generate it everyday with this query.

I am using MS SQL Server 2008. Here is a sample test data.

Sample DateTimeField Data

Could you give me an idea how am i going to do it

SELECT transaction
FROM test_tbl
WHERE ...

Upvotes: 0

Views: 13666

Answers (4)

Romen Dey
Romen Dey

Reputation: 1

SELECT created_at, id FROM tbl_invoice_item WHERE date(created_at) between date('2018-11-26') AND date('2018-11-28') AND time(created_at) BETWEEN '12:00' AND '12:40'

Upvotes: 0

Neo
Neo

Reputation: 135

You can use "between" clause to find the records that lie between the two end dates (inclusive) :

TRY:

SELECT transaction
FROM test_tbl

where datetime between 
cast(convert(varchar, DATEADD(dd,-1, GETDATE()), 101)+' 6:59 PM' as datetime) --to get yesterday's date and time at 6:59 *hardcoded*
and 
cast(convert(varchar, getdate(), 101)+' 7:00 PM' as datetime) --to get current date at 7pm

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can use the column containing the date time field in WHERE clause, if your column is transactionDatTim then query is like below:

DECLARE @tempDate DATE -- note that this date and not DateTime
DECLARE @tempStartDateTime DATETIME, @tempEndDateTime DATETIME
SET @tempDate = GETDATE()
SET @tempEndDateTime = DATEADD(hh,19,CAST(@tempDate AS datetime))
SET @tempStartDateTime  = DATEADD(mi,-1,DATEADD(d,-1,@tempEndDateTime))


--the query
SELECT transaction
FROM test_tbl
WHERE transactionDatTim BETWEEN @tempStartDateTime  AND @tempEndDateTime

shorter version w/o temp variables will be

--the query
SELECT transaction
FROM test_tbl
WHERE transactionDatTim BETWEEN 
   DATEADD(mi,-((24-19)*60+1),CAST(CAST(GETDATE() AS DATE) AS datetime))  
    AND 
   DATEADD(hh,19,CAST(CAST(GETDATE() AS DATE) AS datetime))

Upvotes: 4

Kantanand US
Kantanand US

Reputation: 108

Your can try this and see 

select transaction from test_tb1 where transaction between '2017-05-02 18:59:00' and '2017-05-03 19:00:00';

Upvotes: 0

Related Questions