<= and comparing strings

Hi,

I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707

What I want to to is to search for log posts that are in between two dates.

I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.

How would I solve this

Best /M

Well, I would suggest you convert that data into a datetime value and you will get a lot more value. But, the problem you are having is that 'A' comes before 'AA' in a sort order. So, 070422 is before any value in your table that has a time portion. So use < 070423 and you will get the values you need:

drop table test

go

create table test

(

timecreated char(16)

)

go

insert into test

select '0704212112538707'

union all

select '0704223112538707'

union all

select '0704212112338707'

union all

select '0704242112338707'

go

select *

from test

where timeCreated >= '070421'

and timeCreated < '070423'

timecreated
-
0704212112538707
0704223112538707
0704212112338707

'0704242112338707' was not returned.

|||You cannot compare strings in this manner and get teh results you are wanting. You will need to convert the field to a datetime for comparisons.
|||

What if you sum 1 day to the final timecreated?. I mean, of you want the '070421' to '070422' you can probe with the '070422' + 1 day -> '070421' to '070423'