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’)
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
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
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.
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