Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Advanced where  between but only for time, not for date.

Posted on 2009-04-14
6
Medium Priority
?
204 Views
Last Modified: 2012-05-06
Hi expert!

Im using SQL Server 2005 and have question. Look on the picyure.  I need to apply criteria over date like this
WHERE  (dbo.LTimeSlots.StartDateTime BETWEEN dbo.LCampaignTimes.BTime AND dbo.LCampaignTimes.ETime)

But I need to check only for part of time (Hour, Minutes, second) and to ignore data part  (year, month, day) which is total different.   How to do this most effective way,  I have index over dbo.LTimeSlots.StartDateTime ?

Thank you!

sql-Date.JPG
0
Comment
Question by:dvplayltd
[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
  • 3
  • 2
6 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24142622
Hello dvplayltd,

This will be slow...

WHERE  CONVERT(varchar, dbo.LTimeSlots.StartDateTime, 8) BETWEEN CONVERT(varchar, dbo.LCampaignTimes.BTime, 8) AND CONVERT(varchar, dbo.LCampaignTimes.ETime, 8)

Regards,

Patrick
0
 
LVL 12

Accepted Solution

by:
Nathan Riley earned 1500 total points
ID: 24142643
Here you go:
WHERE (dbo.LTimeSlots.StartDateTime BETWEEN convert(datetime,dbo.LCampaignTimes.BTime,108) AND convert(datetime,dbo.LCampaignTimes.ETime,108))

Open in new window

0
 

Author Comment

by:dvplayltd
ID: 24142936
To Gallitin

Is it possible to forget to apply Convert also for StartDateTime ? I mean to be:

WHERE (convert(datetime,dbo.LTimeSlots.StartDateTime,108)  BETWEEN convert(datetime,dbo.LCampaignTimes.BTime,108) AND convert(datetime,dbo.LCampaignTimes.ETime,108))

And this convert convert to time, or to sring ? Becaues if is to string it will not work property ?
 
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24143721
dvplayltd said:
>>Is it possible to forget to apply Convert also for StartDateTime ? I mean to be:

Please look at my first post--that is exactly what I did :)

Patrick
0
 

Author Comment

by:dvplayltd
ID: 24143767
To matthewspatrick

I find best solution, here to you to know it :-). It is REALLY faster .. difrence is about 0.5 sec. over 0.6 sec. total time ..

      WHERE      (dbo.LTimeSlots.StartDateTime - CAST(FLOOR(CAST(dbo.LTimeSlots.StartDateTime AS float)) AS datetime)
          BETWEEN      (@BTime - CAST(FLOOR(CAST(@BTime AS float)) AS datetime))  AND
                              (@ETime - CAST(FLOOR(CAST(@ETime AS float)) AS datetime))  )
0
 

Author Comment

by:dvplayltd
ID: 24143780
Yes, but in first post you convert it to varchar, then compare it. I do not think that compare like nvarchar is egual to compare number and may result wrong result.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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