Re: Help Plz: Sql statement

From: Tony <andrewst_at_onetel.net.uk>
Date: 21 Mar 2003 13:13:43 -0800
Message-ID: <c0e3f26e.0303211313.2883027e@posting.google.com>

jim_kangosjarvi_at_hotmail.com (Jim) wrote in message news:<b2464361.0303210253.343a5487_at_posting.google.com>... > This is production data and date is stored like 1030101 for 01/01/2003

What a poor production system you have - never heard of the "Millennium Bug"?

But anyway...

> > > select * from table where (StartDate >= 031201 and StartTime >= 0600)
> > > and (EndDate <= 031203 and EndTime =< 0600

Your problem is that your query selects only the records where a) StartDate >= 031201 and EndDate <= 031203 AND
b) StartTime >= 0600 and EndTime =< 0600

What you want is:

WHERE TO_DATE(StartDate||StartTime,'YYMMDDHH24MI') >= TO_DATE('0312010600','YYMMDDHH24MI')
AND TO_DATE(EndDate||EndTime,'YYMMDDHH24MI') <= TO_DATE('0312030600','YYMMDDHH24MI') or if you prefer to avoid DATEs (!):

WHERE StartDate||StartTime >= '0312010600' AND EndDate||EndTime <= '0312030600' Received on Fri Mar 21 2003 - 15:13:43 CST