0

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.

Community
  • 1
  • 1
Bevan
  • 566
  • 4
  • 7
  • 19

2 Answers2

1

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" )
Eric T
  • 1,026
  • 3
  • 20
  • 42
  • This looked promising, but it falls over saying 'incorrect syntax near "=" ' and "incorrect column name 'datetime'" – Bevan Dec 16 '13 at 03:24
  • Maybe i was confusing in my OP. where i wrote `datetime=`, i am not sure what the correct thing to put here is. – Bevan Dec 16 '13 at 03:29
  • 1
    Hi please read the edited answer above and reply if it still return in syntax error. – Eric T Dec 16 '13 at 03:41
  • Msg 195, Level 15, State 10, Line 3 'date' is not a recognized built-in function name. I inserted the entire query string below – Bevan Dec 16 '13 at 03:46
  • Hi there are you doing php mysql? – Eric T Dec 16 '13 at 03:58
  • Sorry, SQL2008 R2, query by HTML using webmatrix. cshtml file that queries the sql directly. – Bevan Dec 16 '13 at 09:28
  • Msg 195, Level 15, State 10, Line 7 'CURDATE' is not a recognized built-in function name. – Bevan Dec 16 '13 at 17:51
1

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'
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • Hmmm.Entire query: `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 (Declare @i date=getdate()Declare @j varchar(10)='07:00' select DATEADD(day, DATEDIFF(day, 0, @i), @j)) and (datetime = date("Y/m/d"). ' 10:00:00') and SOHIS.EVENT_TYPE = 'I'` which returns error near declare. – Bevan Dec 16 '13 at 03:43
  • I re-arranged this and it works fine. Thanks to you all! @Eric T : my system didn't like `curdate` or `concat` - although I understood what you were suggesting. Turned out this `Declare` trick was the key. – Bevan Dec 16 '13 at 19:20
  • Great, glad that you found the way : ) – Eric T Dec 17 '13 at 01:35