Oracle Date datatype strange behavior. 1

[ajacode]

Our Crystal report (ver 8.5) uses the inbound parameters of an Oracle SP to filter the date range for a listing of recorded calls.

Using Oracle 8.17 we have a record in a table called Phone_Log_tbl in the Field (Call_Date, Date) that is recorded with a date of April 17,2003.

We have an Oracle Stored Procedure which has 2 Date parameters (Start_Date, End_Date) working against the aforementined Call_Date field.


When the date range is filtered Start_Date as 04/17/2003 and End_Date as 04/17/2003 the report returns no records from the 17th. However if the date range is filtered Start_Date as 04/17/2003 and End_Date as 04/18/2003 ( a typical user might say this is a 2 day date range) then the items for 4/17 show up.

This starts me to thinking: if I want a Date Range of say 1/1 to 1/15 do I need 1/1/ to 1/16 to show the real data? Or to take it one step further.. do I need 12/31 to 1/16 to really show the data from 1/1 to 1/15?

Thanks

AJ

 

[carp]

The Oracle DATE datatype includes a time (hour/minute/second) component. If you don't specify a time component, Oracle assumes midnight.

So if you pass in start date = 4/7/03 and end date = 4/7/03, Oracle will set both parameters to exactly midnight. So to get everything on the 7th, you would need to enter 4/7/03 to 4/8/03, which would give you the span of 7 Apr 2003:00:00:00 to 8 Apr 2003:00:00:00.

 

[BJCooperIT]

Oracle dates are truly datetime formats. A date column can be precise down to the seconds. If the Call_Date column was valued with sysdate it might contain 04/17/2003 09:45:58am. If stated in 'yyyymmddhh24miss' format this would be:
20030417094558

The comparison:

if   CALL_DATE between
     to_date('04/17/2003','mm/dd/yyyy') and
     to_date('04/17/2003',/mm/dd/yyyy') then
.....
end if;

would actually be like saying:

if   '20030417094558' between
     '20030417000000' and
     '20030417000000' then
.....
end if;

which evaluates to false. Your end date parameter needs to end at 11:59:59pm to catch all the timeframes during the day. You could say between begin_date and (end_date + 1).

Hope this helps,

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[BJCooperIT]

Carp,
You type faster than I do! But, I like your consise way presenting it better than my longwinded version.

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[ajacode]

Coop,

You BOTH added value, your explaination made it very understandable.

The problem with the User to type in a day extra is we are diplaying the inbound params as a "Date To" display.

We need to do math

I know in SQL Server its the DateAdd function but could you be so kind ans to tell me how to do it in Qracle

I have now Between Start_Date AND End_Date

Could it be as simple as (End_Date +1) ?? That would be VERY UN ORacle like <grin>

Thanks so much to both of you

AJ

 

[Turkbear]

Hi,
I believe you can use standard math to add days to any Oracle date...The same 'time' factor will apply ..

(Start_Date + 2) would add 2 days to the start date...
so if Start_Date was 9/1/2003 ( Midnight assumed if not specified), (Start_Date + 2) will be
9/3/2003 ( again Midnight) ..

 

[BJCooperIT]

Exactly Turkbear!

The only word of caution here is that if CALL_DATE
were: 04/17/2003 09:45:58am and you code:

(CALL_DATE + 1)

the result would be 04/18/2003 09:45:58am

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}

 

[BJCooperIT]

I should also mention that if:
CALL_DATE = 04/17/2003 09:45:58am

and you code:

trunc(CALL_DATE)

the resulting value is the date set to midnight:
CALL_DATE = 04/17/2003 00:00:00am

***A DATE TRICK***
Use date functions to create spelled out numbers:

select init_cap(
       to_char(to_date(700,'J'),'JSP'))
from   DUAL

result: Seven Hundred

select * from Life where Brain is not null

Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
^{When posting code, please use TGML for readability. Thanks!}