Re: Help Plz: Sql statement

Jim wrote:
> Sorry.
>
> This is production data and date is stored like 1030101 for 01/01/2003

Ugly. But I guess you just ported a mainframe or flat file DB to ORACLE right? Ask your boos if you can do a proper conversion next time.

> But how to make an report that gives a production report for actions
> done in a dayspan given by the user
> select * from table where (StartDate >= 031201 and StartTime >= 0600)
> and (EndDate <= 031203 and EndTime =< 0600
> Then the production for 0031202 aint included coz the starttime is not
> fullfilled.
> I could leave out time but production done at the end date after 0600
> should not be included. same goes for production done before 0600
> startdate
>
> regards Jim
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3e75da7f$0$21987$ed9e5944_at_reading.news.pipex.net>...
>

>>please tell me this is a college assignment, in production your date fields
>>should be dates.
>>Meanwhile look at the to_date function.
>>

>>>select * from table where (StartDate >= 031201 and StartTime >= 0600)
>>>and (EndDate <= 031203 and EndTime =< 0600
>>>
>>>Sql statement should give all rows
>>>but X2 wont be given back because StartTime fails, but it should
>>>because the action is done day between start date and end date.
>>>
>>>How should this sql look like?
>>>
>>>Regards Jim
>>

You want something that's after a given timestamp which in your case is in two fields (Niall already mentioned that poor decision). So following up on his suggestion, you should convert to an ORACLE DATE using the TO_DATE function. I'll give you one example using the hardcoded values and you do the rest:

TO_DATE('031201' || '0600', 'YYMMDDHH24MI') I assumed a 24hour clock since you did not specify AM or PM anywhere. Additional hint was leaving the date and time as separate strings.

A final suggestion: look up BETWEEN

HTH <shameless plug mode on>
Let me know when you start to convert to normalized data. It's one of the areas I work on, ie conversions.
<shameless plug mode off>

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost