Query with Time | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query with Time

Hi, 1st post so hopefully I make sense. Here is my query. I’m trying to get all records between 4/1 and 4/10 that were entered 15 minutes after 7am. The Date part works, but when I add the LogTime query I end up with 0 records. SELECT *
FROM dbo.FSS_ItemHistory
WHERE (LogDate BETWEEN ‘4/1/2006’ AND ‘4/10/2006′) AND (LogTime BETWEEN ’07:00:00 AM’ AND ’07:15:00 AM’)
Both LogDate and LogTime datetime Data Types and are both indexed. It is a simple query so I hope it a simple resolution. Thanks

You must use the 24H format for time, so drop the AM bits.
That still did not solve the issue. SELECT *
FROM dbo.FSS_ItemHistory
WHERE (LogDate BETWEEN ‘4/1/2006’ AND ‘4/10/2006′) AND (LogTime BETWEEN ’07:00′ AND ’07:15’)
or SELECT *
FROM dbo.FSS_ItemHistory
WHERE (LogDate BETWEEN ‘4/1/2006’ AND ‘4/10/2006′) AND (LogTime BETWEEN ’07:00:00′ AND ’07:15:00’)
Here are a few of the records that it should be returning to give you an idea of what the data looks like in the table.
ItemIndex LogDate LogTime LogCode
8625954/1/20067:00:26 AM62
8625954/1/20067:00:30 AM3
9999994/1/20067:00:31 AM61
5240764/1/20067:00:44 AM20
5240764/1/20067:00:45 AM56
9999994/1/20067:00:56 AM200
9999994/1/20067:01:36 AM200
9999994/1/20067:01:45 AM200
5256474/1/20067:01:53 AM8
5409384/1/20067:02:28 AM42
5409384/1/20067:02:32 AM35
5256474/1/20067:02:32 AM10
5240764/1/20067:02:35 AM60
5240764/1/20067:02:35 AM57
5409384/1/20067:02:53 AM301
5409384/1/20067:02:54 AM3
5240764/1/20067:05:09 AM62
5240764/1/20067:05:12 AM3
9999994/1/20067:05:13 AM61
6559634/1/20067:06:23 AM2
6559634/1/20067:06:45 AM8
6559634/1/20067:06:59 AM10
9999994/1/20067:07:02 AM200
348774/1/20067:07:24 AM2
348774/1/20067:07:36 AM8
110804/1/20067:07:36 AM2
110804/1/20067:07:37 AM2
8449204/1/20067:07:51 AM55
348774/1/20067:07:51 AM10
8449204/1/20067:07:51 AM2
110804/1/20067:07:57 AM8
Thanks for your help
Even this alone still returns 0 rows. SELECT *
FROM dbo.FSS_ItemHistory
WHERE (LogTime BETWEEN ’00:00:00′ AND ’23:59:59′)
But this returns everything including dates between 7:00 and 7:15
SELECT *
FROM dbo.FSS_ItemHistory
WHERE (LogTime NOT BETWEEN ’00:00:00′ AND ’07:00:00′) AND (LogTime NOT BETWEEN ’00:07:15′ AND ’23:59:59′) When really it should just return the stuff between 7:00 and 7:15 I just don’t understand why the query is not working. I did check and the Here are the design settings for the table. http://www.slibe.com/images/942215e8-SQLDesignView_jp.jpg
I can’t access tohttp://www.slibe.com/images/942215e8-SQLDesignView_jp.jpg Can you post your table structure here instead ? You can use Enterprise Manager to generate the SQL Script for your table
KH
Your logintime column is datetime datatype and you cant compare it with only time. Refer this
http://vyaskn.tripod.com/searching_date_time_values.htm Madhivanan Failing to plan is Planning to fail
Thanks for the info Madhivana, My Query now works.
SELECT *
FROM dbo.FSS_ItemHistory
WHERE (LogTime – CAST(FLOOR(CAST(LogTime AS float)) AS datetime) BETWEEN ‘7:00’ AND ‘7:15’) AND (LogDate BETWEEN ‘4/1/2006’ AND ‘4/10/2006’)
ORDER BY LogDate, LogTime
Well. Read that article fully.
Also readhttp://www.sql-server-performance.com/fk_datetime.asp Madhivanan Failing to plan is Planning to fail
]]>