Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-04-14
6
Medium Priority
?
206 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

972 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