January 16, 2008 at 3:41 am
Hi,
I have taken over a system that stores dates in 3 seperate columns - day, month, year.
I am experiencing issues when trying to scan date ranges. My scans work fine when converting the 3 values into a valid date however don't when I attempt a scan using them as integers.
This will return 10k rows...
SELECT * from myTable where CONVERT(DATETIME,CONVERT(varchar, day) + '-' + CONVERT(varchar, month) + '-' + CONVERT(varchar, year))
BETWEEN '1/10/2007' AND '16/12/2007'
But this returns only 2k rows...
select * from ee_orders WHERE
(day >= 1 AND month >= 10 AND year >= 2007)
AND
(day <= 16 AND month <= 12 AND year <= 2007)
Can anyone point out why this is happening?
Sometimes I get the same number of rows returned in each - depending on the date values entered.
Joe
January 16, 2008 at 3:58 am
Quit logic..
If you convert your day / month / year column to int you will get
01012008
21012008
01022008
15022008
and it is integer, so there is no knowlage of date nor time.
So you need to handle it year / month / day
20080401 april first 2008
You'll have to convert your variables also to int to be able to have it working.
Shame on me. I jumped to conclusions way to early !
it is your and combination that kills it.
e.g. day >= 1
and day<= 16
So you skip all days >16
By converting to datetime, you'll have the full flavour of functions for date handling available, which will not be the case with int.
e.g. you can compare with a datetime column/string
Basic rule number one for any DBMS: use the correct datatype !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2008 at 4:04 am
Iam with alzdba there. Use the correct datatype.U will hv to use the first query to get the correct results.
Had there being a datetime column you would'nt have these issues and u could put a index on this datetime column whihc would have resulted in a faster query.
Any chance of doing it like this ?
"Keep Trying"
January 16, 2008 at 4:10 am
it is your and combination that kills it.
e.g. day >= 1
and day<= 16
So you skip all days >16
By converting to datetime, you'll have the full flavour of functions for date handling available, which will not be the case with int.
e.g. you can compare with a datetime column/string
Basic rule number one for any DBMS: use the correct datatype !
Ah... yes of course - that's it.
Many thanks - I've been staring at it too long!
Joe
January 16, 2008 at 6:07 am
well maybe, just maybe, ... since you're on SQL2005 ....
you may want to add a computed_column of the datetime datatype and put an index on it.
check BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8d17ac9c-f3af-4bbb-9cc1-5cf647e994c4.htm
create table tmpTb (cday int, cmonth int, cyear int)
set nocount on
insert into tmpTb values (1,1,2008)
insert into tmpTb values (20,1,2008)
-- insert into tmpTb values (34,1,2008) -- will generate an error at usage time !!!
set nocount off
alter table tmpTb
add cdate AS CONVERT(DATETIME,CONVERT(varchar, cmonth )
+ '/' + CONVERT(varchar, cday )
+ '/' + CONVERT(varchar, cyear), 101)
go
select *
from tmpTb
go
create index x_tmpTb on tmpTb (cdate);
go
select *
from tmpTb
where cdate between '2006-12-31' and '2008-01-18'
drop table tmpTb
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2008 at 6:33 am
Thak you very much - that has worked and solved a number of other issues I had.
Joe
January 16, 2008 at 7:48 am
Just keep in mind you should get rid of the day , month and year columns to be safe for the future.
That may have heavy application impact, depending on how much these columns are used !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 16, 2008 at 8:04 am
ALZDBA (1/16/2008)
well maybe, just maybe, ... since you're on SQL2005 ....you may want to add a computed_column of the datetime datatype and put an index on it.
check BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8d17ac9c-f3af-4bbb-9cc1-5cf647e994c4.htm
create table tmpTb (cday int, cmonth int, cyear int)
set nocount on
insert into tmpTb values (1,1,2008)
insert into tmpTb values (20,1,2008)
-- insert into tmpTb values (34,1,2008) -- will generate an error at usage time !!!
set nocount off
alter table tmpTb
add cdate AS CONVERT(DATETIME,CONVERT(varchar, cmonth )
+ '/' + CONVERT(varchar, cday )
+ '/' + CONVERT(varchar, cyear), 101)
go
select *
from tmpTb
go
create index x_tmpTb on tmpTb (cdate);
go
select *
from tmpTb
where cdate between '2006-12-31' and '2008-01-18'
drop table tmpTb
This is a probably about fastest algorithim for doing the conversion of Year, Month, and Day integers to datetime:
alter table MyTable
add MyDate as
dateadd(month,(12*MyYear)-22801+MyMonth,MyDay-1)
You can see a discussion of this problem on the link below:
Converting Year, Month, and Day to DateTime
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply