Date field as char - query not picking data 1

[mutley1]

Hi all,

I have a table that stores dates as a char (15) and have a query that seems to miss the last date off the end when i specify a >= and <= clause.

Example: today at 12:01:22 would be stored as 200706071201221

My code is shown below

select regdatetime, 
substring (regdatetime, 1, 8), 
substring (regdatetime, 9, 2), 
count (uri) from tsrecord 
where rcrectypeuri in (select uri from tsrectype where containerlevel in (2, 4)) 
and regdatetime >= '20070514%' 
and regdatetime <= '20070518%' 

group by regdatetime 
order by regdatetime

This should pick up everything between 14th and 18th (inclusive), but for some reason it seems to drop the last day (unless I change it to the 19th and then it picks up the 18th. There is definately data for the 18th but I cans see what I am doing wrong in the original code.

Any help would be much appreciated!

M.

 

[hooggie]

Change your parameters to

and regdatetime >= '20070514'
and regdatetime <= '200705189999999'

By wildcarding you are forcing it to look for items less the earliest time on that date

 

[mutley1]

Thanks hooggie,

Gotcha. So simple but I've gone myopic with getting the other clauses right because someone wanted the info so quick the other day. I can fine tune it now.

Cheers,

Matt.