CODEWITHSUNDEEP
sqldatetime
Bevan
Bevan

Reputation: 584

How to get a specific time on a specific day SQL query

I'm looking to do something similar to Simple DateTime sql query but with a difference: I want to specify the time (7.00 am) on today's date.

I want to count the number of records that match the criteria at that time:

SELECT COUNT(SEQNO)    
FROM TABLE    
WHERE [CRITERIA]     
and [datetimecolumn] between (datetime=[today's date]at 7.00am)
and (datetime=[today's date]at 10.00am)

I can do it with date alone, but the time bit has me confused.

Upvotes: 0

Views: 377

Answers (2)

Eric T
Eric T

Reputation: 1026

Not sure if this will help you as I assume 7am and 10am would remain the same as it is.

============================================

BETWEEN syntax should be something like this

mysql> SELECT * FROM employee_tbl 
    -> WHERE daily_typing_pages BETWEEN 170 AND 300;

Credited to this site

So your code should be: (Please include the brackets for the datetimecolumn)

and ([datetimecolumn] between CONCAT( CURDATE(),  " 07:00:00" )
and CONCAT( CURDATE() ,  " 10:00:00" )

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

try this

 Declare @i date=getdate()
Declare @j varchar(10)='07:00' 
Declare @k varchar(10)= '10:00:00' 

select COALESCE(COUNT(DISTINCT SOH.SEQNO),0) from dbo.SALESORD_HDR SOH 
inner join SALESORDHIST SOHIS on SOH.SEQNO = SOHIS.HEADER_SOURCE_SEQ 
where SOHIS.HISTDATETIME between ( DATEADD(day, DATEDIFF(day, 0, @i), @j)
and DATEADD(day, DATEDIFF(day, 0, @i), @k))
and SOHIS.EVENT_TYPE = 'I'

Upvotes: 1

Related Questions