CODEWITHSUNDEEP
sqlsql-server-2008-r2
Haminteu
Haminteu

Reputation: 1334

Select Date with Specific Time SQL

I have the following table:

oDate          oTime          oAct
--------------------------------------
2017-06-01     00:00:00       A
2017-06-01     01:00:00       B
2017-06-01     02:00:00       C
ff.
2017-06-02     00:00:00       B
ff.

I want to select a day before (only after 21:00:00) and after.
Let say, If I Select '2017-06-02' then the result should be:

oDate          oTime          oAct
--------------------------------------
2017-06-01     22:00:00       A
2017-06-02     00:00:00       B
2017-06-02     01:00:00       C
ff.
2017-06-03     00:00:00       C
ff.

Also, for the query. I only have one parameter, which is @oDate date.
Please advise.
Thank you.

Upvotes: 0

Views: 46

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35613

If I follow your question correctly I think you are after a where clause such as this:

select
*
from YourTable
where (
       oDate > '20170602'
      OR
       (oDate = '20170602' AND oTime >= '21:00:00')
      )

This would give you all dates after 2017-06-02 as well as times on that date at and after 21:00

To make best use of indexes that may exist on those columns I suggest you do not try to combine the date with time such as this dateadd(day,datediff(day,0,oDate),oTime) and then try to filter >= '20170602 21:00:00' as that would produce table scanning.

perhaps this will help

select
*
from YourTable
where (
       oDate > @dateparameterhere
      OR
       (oDate = @dateparemterhere AND oTime >= '21:00:00')
      )

Upvotes: 2

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Try this concatenating fields to make a datetime field

select 
* from table
where 
cast (cast(odate as varchar(max)) + ' ' + cast(otime as varchar(max))  as datetime) < cast('2017-06-02 21:00:00' as datetime)

Upvotes: 0

Related Questions