jamppi
asked on
sql query date time
Hi!
i have 2 datetime fields OnDateTime, OfDateTime
OnDateTime 2012-04-02 18:00:00
OffDateTime 2012-12-01 23:00:00
the query needs to return data between the dates but only between 18:00:00 -23:00:00 every day between the 2 dates. i'e it should not return anything if query is run 2012-04-03 16:00:00 but it is to return data if query is run 2012-04-03 18:01:00. for now i'm dealing with this programatically but i would like to use a query for it.
i have 2 datetime fields OnDateTime, OfDateTime
OnDateTime 2012-04-02 18:00:00
OffDateTime 2012-12-01 23:00:00
the query needs to return data between the dates but only between 18:00:00 -23:00:00 every day between the 2 dates. i'e it should not return anything if query is run 2012-04-03 16:00:00 but it is to return data if query is run 2012-04-03 18:01:00. for now i'm dealing with this programatically but i would like to use a query for it.
Or
select * from <TableName>
where (convert(nvarchar,OnDateTime,108) >= '18:00:00' and convert(nvarchar,OffDateTime,108) <= '23:00:00')
and (convert(nvarchar,OnDateTime,101) >= '04/02/2012' and convert(nvarchar,OffDateTime,101) <= '12/01/2012')
Do OnDateTime and OffDateTime represent periods that you need to see if they intersect with the periods you are looking at?
If OnDateTime = '2012-04-02 17:00:00' AND OffDateTime = '2012-04-03 02:00:00' would it show up?
Are both fields on the same row in the table?
For instance, we use similar fields when doing resource tracking, and need to see whomever had a resource during each shift. There are four options:
1. Have it assigned prior to shift and keep until after shift
2. Have it assigned prior to shift and return it during shift.
3. Have it assigned during shift and return after shift
4. Have it assigned during shift and returned during shift.
Which conditions are you looking for?
If OnDateTime = '2012-04-02 17:00:00' AND OffDateTime = '2012-04-03 02:00:00' would it show up?
Are both fields on the same row in the table?
For instance, we use similar fields when doing resource tracking, and need to see whomever had a resource during each shift. There are four options:
1. Have it assigned prior to shift and keep until after shift
2. Have it assigned prior to shift and return it during shift.
3. Have it assigned during shift and return after shift
4. Have it assigned during shift and returned during shift.
Which conditions are you looking for?
ASKER
OndateTime OffDateTime Data
2012-04-02 18:00:00 2012-10-02 21:00:00 test 1
2012-04-02 19:00:00 2012-10-02 21:00:00 test 2
2012-04-02 20:00:00 2012-10-02 21:00:00 test 3
2012-04-02 21:00:00 2012-10-02 22:00:00 test 4
2012-04-03 18:00:00 2012-10-02 23:00:00 test 5
If I'm querying the database at 2012-04-02 12:00:00 (getdate()) it should not return anything as you can see.
But if I'm querying at 2012-04-03 19:01:00 i should get test1 and test 2
I hope this clarifies the question
2012-04-02 18:00:00 2012-10-02 21:00:00 test 1
2012-04-02 19:00:00 2012-10-02 21:00:00 test 2
2012-04-02 20:00:00 2012-10-02 21:00:00 test 3
2012-04-02 21:00:00 2012-10-02 22:00:00 test 4
2012-04-03 18:00:00 2012-10-02 23:00:00 test 5
If I'm querying the database at 2012-04-02 12:00:00 (getdate()) it should not return anything as you can see.
But if I'm querying at 2012-04-03 19:01:00 i should get test1 and test 2
I hope this clarifies the question
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect!
Thank you for the quick solution.
Thank you for the quick solution.
Open in new window