Solved

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

Posted on 2009-04-14
6
184 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
  • 3
  • 2
6 Comments
 
LVL 92

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 11

Accepted Solution

by:
N R earned 500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 92

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 36
MS SQL page split per second is high 19 74
Divide by zero error encountered. 2 33
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 40
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now