Solved

sql query date time

Posted on 2012-04-02
6
256 Views
Last Modified: 2012-04-02
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.
0
Comment
Question by:jamppi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37795893
Try this

select * from <TableName>
where convert(nvarchar,OnDateTime,108) between '18:00:00' and '23:00:00'
and convert(nvarchar,OffDateTime,108) between '18:00:00' and '23:00:00'
and convert(nvarchar,OnDateTime,101) between '04/02/2012' and '12/01/2012' 
and convert(nvarchar,OffDateTime,101) between '04/02/2012' and '12/01/2012' 

Open in new window

0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 37795901
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')

Open in new window

0
 
LVL 18

Expert Comment

by:lludden
ID: 37796005
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?
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:jamppi
ID: 37796042
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
0
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
ID: 37796168
I am still not 100% sure of what you want, but here is a try.  If this isn't it, post the code you are using and I can convert it to a query.

DECLARE @T1 TABLE (OnDatTime DATETIME, OffDateTime DATETIME, Data VARCHAR(10))
INSERT INTO @T1 
SELECT '2012-04-02 18:00:00', '2012-10-02 21:00:00','test 1' UNION
SELECT '2012-04-02 19:00:00', '2012-10-02 21:00:00','test 2' UNION
SELECT '2012-04-02 20:00:00', '2012-10-02 21:00:00','test 3' UNION
SELECT '2012-04-02 21:00:00', '2012-10-02 22:00:00','test 4' UNION
SELECT '2012-04-03 18:00:00', '2012-10-02 23:00:00','test 5'

DECLARE @QueryAsOf datetime = '2012-04-03 19:01:00'

SELECT Data FROM @T1 T1
WHERE cast(convert(VARCHAR(5),@QueryAsOf,108) AS DATETIME) BETWEEN cast(convert(VARCHAR(5),OnDatTime,108) AS DATETIME) AND cast(convert(VARCHAR(5),OffDateTime,108) AS DATETIME)
AND DATEADD(DAY, DATEDIFF(DAY, 0, T1.OnDatTime), 0) < DATEADD(DAY, DATEDIFF(DAY, 0, @QueryAsOf), 0)

Open in new window

0
 

Author Closing Comment

by:jamppi
ID: 37796574
Perfect!
Thank you for the quick solution.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question