SQL Query - Select Time

Hi Experts,
This is my query:
SELECT * FROM WorkSchedules WHERE StartTime <= '1899-12-30 09:30:00.000'
How can I ignore the date and select records that are earlier than 09:30
I would like to achieve this because when a record is updated, it becomes 1900-01-01 09:30:00.000 in the SQL Server database, and my query won't return the expected rows.
I would appreciate your help.
romsomAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
WHERE (DATEPART(HOUR, StartTime) < 9 OR
              (DATEPART(HOUR, StartTime) = 9 AND DATEPART(MINUTE, StartTime) <= 30))

Note: That is not "sargable" and could force a scan of the entire table.

Btw, why are you using a base date of "1899-12-30"?  In SQL, it's vastly more common to use 1900-01-01.
0
 
romsomAuthor Commented:
Thank you very much, this is perfect!
0
 
Brian CroweDatabase AdministratorCommented:
Note: This is going to require a table scan since the predicate is non-sargable

SELECT *
FROM WorkSchedules
WHERE CAST(StartTime AS TIME) < '09:30'
1
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
romsomAuthor Commented:
Scott, I'm importing the data from an Excel sheet to SQL Server.
After the import the StartTime column automatically adds the 1899-12-30 date, although in my Excel sheet the time is just '09:30'
When a user updates a record from the web site, the date changes to 1900-01-01.
Thank you for your help, your solution was perfect!
0
 
romsomAuthor Commented:
Brian, I've tried your solution as well, and it also returns the expected results. Sorry, I already closed the question.
0
 
PortletPaulfreelancerCommented:
The "base date" in Excel is different to that of SQL Server, which is why you are getting 1899-12-30

If you only have the 2 dates to deal with, then this may be faster.

SELECT * FROM WorkSchedules
 WHERE StartTime between  '1899-12-30 00:00:00.000' and '1899-12-30 09:30:00.000'
 OR StartTime between  '1900-01-01 00:00:00.000' and '1900-01-01 09:30:00.000'

But if you aren't interested in the date, why not have a computed&persisted column that is just of the time?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.