January 7, 2003 at 5:34 pm
I am running SQL 7.0 Service Pack 3
Also noteable, is that this is a temporary server that we recovered to after a hardware failure. Our production server was clustered and running enterprise edition. The temporary server we used to recover is not clustered, and therefore we just installed SQL standard edition.
The script below illustrates the issue I am seeing. When converting dates to a char(10), and comparing them, I lose any dates that are in 2003. Anyone have any ideas?:
CREATE TABLE tmp_employee (empid int, hire_date datetime)
INSERT INTO tmp_employee
VALUES (1, getdate()-10)
INSERT INTO tmp_employee
VALUES (2, getdate()-8)
INSERT INTO tmp_employee
VALUES (3, getdate())
--All Records in tmp_employee
select * from tmp_employee
empid hire_date
----------- ------------------------------------------------------
1 2002-12-28 17:02:30.610
2 2002-12-30 17:02:30.610
3 2003-01-07 17:02:30.627
--this does not bring back any data for 2003 (WHY?)
select *
from tmp_employee
where convert(char(10),hire_date,101) between '12/1/2002' and '12/31/2003'
empid hire_date
----------- ------------------------------------------------------
1 2002-12-30 00:00:00.000
2 2002-12-31 00:00:00.000
--this conversion WILL bring back data from 2003
select *
from tmp_employee
where convert(datetime,convert(char(10),hire_date,101)) between '12/1/2002' and '12/31/2003'
empid hire_date
----------- ------------------------------------------------------
1 2002-12-28 17:02:30.610
2 2002-12-30 17:02:30.610
3 2003-01-07 17:02:30.627
January 8, 2003 at 2:04 am
Because in the first case you are not comparing dates but char values, see the following example: -
select t.[c1] from
(
select 'a' as [c1]
unionselect 'b'
unionselect 'c'
) t
where t.[c1] between 'a' and 'b' --Will return letters greater or equal to 'a' and less than or equal to 'b'
c1
----
a
b
(2 row(s) affected)
The second case is comparing dates - the char values '12/1/2002' and '12/31/2003' will be implicitly converted to datetime values as you are comparing them against a datetime value, giving you the results you expect.
Regards,
Andy Jones
.
January 8, 2003 at 3:50 am
I see it now.
As ANdy states in the first it is a char compare not datetime. I forget this sometimes myself. In simple terms look at it this way.
You output from the first would be
12/28/2002
12/30/2002
Which is bewteen your values of 12/1/2002 and 12/31/2003
Look at it this way alpha order it looks this way
01/07/2003
12/28/2002
12/30/2002
because it compares left to right for order.
date order it looks like
12/28/2002
12/30/2002
01/07/2003
because values are compared right to left in segments as any numeric value is.
Suppose you to find everything between '10' and '40' as char values with the following data
1
10
100
2
20
200
3
30
300
4
40
400
well if the values are char types they are alpha order and would output
10
100
2
20
200
3
30
300
4
40
where if numeric type the numeric order and thus
10
20
30
40
is your output
This is just one of many gotchas in SQL. Then on top of this and where things sometimes go grey is the fact SQL will implicitly type all the data in some cases and it is explicitly required in other.
Meaning
If a column is int and I supply a value of '40' it will implicitly apply as 40.
But in the case of where column is typed to char but you need datetime as in your case you have to explcitly cast one value column or search value to datetime and in this case it will implicit the rest.
There are also cases where you have to implicitly cast to what you want to get the desired effect.
January 9, 2003 at 5:09 am
Very good explanations. I would compare as follows
where convert(char(10),hire_date,120) between '2002-12-01' and '2003-12-31'
Far away is close at hand in the images of elsewhere.
Anon.
January 9, 2003 at 8:02 am
Thanks to everyone for your feedback. This was a big help!
ajroney
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply