June 9, 2008 at 9:50 am
Below is my table and some of data
RESULT
ID | Date (this is smalldatetime) | Time (this is integer) | Value
--------------------------------------------------
201 | 8/1/2005| 5 | 34
202 | 8/1/2005| 46 | 66
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
*Date in table stored as 8/1/2005 12:00:00 AM.
Can someone can show me to filter data between
Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
Till now, this is my query
SELECT
t1.ID, t1.[Date], t1.Time,
t1.VALUE
FROM RESULT t1
WHERE CAST(CONVERT(varchar(11),t1.Date,121) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)
>=CAST(DATEADD(hh,6,DATEADD(dd,-7,GETDATE())) AS smalldatetime) AND
CAST(CONVERT(varchar(11),t1.Date,121) + ' ' + STUFF(CAST(t1.Time AS varchar(6)),3,0,':') AS datetime)<CAST(GETDATE() AS smalldatetime)
I got an error below,
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
After do checking, i've seen these problem
TIME=5, it is become TIME=NULL --> it is suppose 00:05 (this is a 12:05 AM)
TIME=46, it is become TIME=NULL --> it is suppose 00:46 (this is a 12:46 AM)
TIME=101, it is become TIME=10:1 --> it is suppose 1:01
TIME=2048, it is become TIME=20:48 --> this is accurate
how to adjust the above query ("my query") to make sure TIME is accurate, then no problem run the query?
June 9, 2008 at 12:51 pm
If I'm reading what you want correctly, this might do it:
select ID, Date, Time, Value
from dbo.Table
where
dateadd(minute, time%100, dateadd(hour, time/100, date)) >= getdate() - 7
and
dateadd(minute, time%100, dateadd(hour, time/100, date)) < getdate()
It won't use indexes the way it's written, because of the calculations on the columns. If that matters (table is big enough), and it probably does, you could add a calculated column to the table using the formula in the above query, and index that, and use that in the query.
Here are a couple of tests I did:
create table #T (
ID int identity primary key,
Date smalldatetime,
Time smallint,
Value tinyint)
insert into #T (Date, Time, Value)
select dateadd(day, t1.number, '1/1/2008'),
isnull(nullif(cast(t2.number as varchar(2)), '0'), '')
+ right('0' + cast(t3.number as varchar(2)), 2),
70
from dbo.numbers t1
cross join dbo.numbers t2
cross join dbo.numbers t3
where t1.number between 0 and 300
and t2.number between 0 and 23
and t3.number between 0 and 59
set statistics time on
set statistics io on
select ID, Date, Time, Value
from #t
where
dateadd(minute, time%100, dateadd(hour, time/100, date)) >= getdate() - 7
and
dateadd(minute, time%100, dateadd(hour, time/100, date)) < getdate()
order by date, time
Average CPU time 420 ms, total time 600 ms, just for the final select statement.
As opposed to:
create table #T (
ID int identity primary key,
Date smalldatetime,
Time smallint,
Value tinyint)
insert into #T (Date, Time, Value)
select dateadd(day, t1.number, '1/1/2008'),
isnull(nullif(cast(t2.number as varchar(2)), '0'), '')
+ right('0' + cast(t3.number as varchar(2)), 2),
70
from dbo.numbers t1
cross join dbo.numbers t2
cross join dbo.numbers t3
where t1.number between 0 and 300
and t2.number between 0 and 23
and t3.number between 0 and 59
set statistics time on
set statistics io on
alter table #t
add DateAndTime as dateadd(minute, time%100, dateadd(hour, time/100, date))
create index IDX_T on #T (DateAndTime) include (Date, Time, Value)
select ID, Date, Time, Value
from #t
where
dateadd(minute, time%100, dateadd(hour, time/100, date)) >= getdate() - 7
and
dateadd(minute, time%100, dateadd(hour, time/100, date)) < getdate()
order by date, time
Average 30 ms CPU, 200 ms total, for the final select.
On the indexed one:
select ID, Date, Time, Value
from #t
where
DateAndTime >= getdate() - 7
and
DateAndTime < getdate()
order by date, time
Cut about 10 ms off the total run time of the select, and took the average CPU time down to 16 ms.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 9, 2008 at 7:01 pm
million thanks. it's work perfectly. :D. your explanation is awesome. 😉
June 10, 2008 at 11:04 am
You're welcome. Glad it worked.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply