Solved

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

Posted on 2009-04-14
6
191 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 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 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