Tom:

1. I do not understand why i have to use to_Date is SQL book says that between should find dates min>=date<=max.

SInce I am passing those as parameters from a web page, how can is use the time in to_date fucntion with the parameter

where date between to_date(p_fromdate,23:59:59) and to_Date(p_todate,23:59:59)

2. if you use bind variables like you say
where dt_col between to_date( :bv1, 'dd-mon-yyyy' )
and to_date( :bv2, 'dd-mon-yyyy' ) + (1-1/24/60/60)

WHERE DO YOU bind variables and what do you use. owa_util.bind_variables

3. I do not undesrtand how going from stateless web app to stateful does not make a differene in speed. On one app I noticed big difference since i am only using one connection rather than connectio per page.

THe app has 100 procedures but packages are not used. DO you think grouping those procedures into packages by functional area would spped the page loading. IT is taking about 5-6 seconds to run the page now.

Thank you,

Tom:

if you are designing a screen and prompting the user to enter dates how would you prompot them to enter format:

DD-MON-YY or
DD-MON-YYYY or
DD/MM/YYYY or
MM/DD/YYYY or
DD/MM/YY or
MM/DD/YY

I am sure user does not like to enter YYYY for year.

How does oracle store date format?

Can you take the user format and re-format it and store it in oracle's format

Tom:

Which column(s) in v$session tells you whether you have stateful or stateless environment.

I did a select on the table but could not figure it out

Select * from table
where date >= to_date('20020101', 'YYYYMMDD')
and date < to_date('20010117', 'YYYYMMDD') + 1

I've also used + .99999 (5 nines) which appears to set the time to 23:59:59 - I find this a wee bit easier to remember than (1-1/24/60/60)!

ie
where date between to_date( p_fromdate, 'dd-mon-yy' )
and to_date( p_todate, 'dd-mon-yy' ) + .99999

Hi Tom,
Using your logic i note that my query is using the Index and is very fast...


select count(*) from my_big_table
where idx_date_col
between to_date('01.04.2002' , 'DD.MM.YYYY') and
last_day(to_date('04.2002' ,'MM.YYYY') ) + 1 - 1/24*60*60) ;

I want to use that to get all records in April , 2002 .

Do you think it's correct ?

Thanks

Nope:

  1  select
  2  (last_day(to_date('04.2002' ,'MM.YYYY') ) + 1 - 1/24*60*60)
  3* from dual
ops$tkyte@ORA817DEV.US.ORACLE.COM> /    

(LAST_DAY(TO_DATE('0
--------------------
02-dec-2001 00:00:00

this:

  1  select
  2  (last_day(to_date('04.2002' ,'MM.YYYY') ) + 1 - 1/24/60/60)
  3* from dual
ops$tkyte@ORA817DEV.US.ORACLE.COM> /

(LAST_DAY(TO_DATE('0
--------------------
30-apr-2002 23:59:59

is what you want (/60/60 NOT *60*60)

 

Hi Tom ,

Thanks for your prompt reply ,

Sorry i forgot the Left Bracket in the Denominator :

It should have been :

select last_day(to_date('04.2002' ,'MM.YYYY') ) + 1 -
1/(24*60*60) from dual;

Wouldn't connection pooling done by modplsql also result in connected but idle sessions with the web app's usernames in v$session, regardless of stateless or statefull configuration ?

One could look in the DAD configuration file (if using modplsql) and look at the stateful = setting (No for stateless, Yes for stateful), or alternatively look at the setting via the DAD admin URL (which we have completely disabled for security reasons).

The file is in $ORACLE_HOME/Apache/modplsql/cfg

Or DAD setting have stateful = No and v$session filtered for session with the appserver machine field looks like this:

select sid,serial#,username,status,server from v$session where machine = 'e250';
SID SERIAL# USERNAME STATUS SERVER
32 22027 HENDRIKAB INACTIVE DEDICATED
33 55660 ALLASTAIRH INACTIVE DEDICATED
34 41854 HENDRIKAB INACTIVE DEDICATED
38 43595 HENDRIKAB INACTIVE DEDICATED
49 56163 HENDRIKAB INACTIVE DEDICATED
58 28926 HENDRIKAB INACTIVE DEDICATED
65 9406 HENDRIKAB INACTIVE DEDICATED
79 47751 HENDRIKAB INACTIVE DEDICATED

As you can see, the same user doesn't always pick up the same session, but if someone else login via the web app, one of the idle entries just get replaced, e.g.:

select sid,serial#,username,status,server from v$session where machine = 'e250';
SID SERIAL# USERNAME STATUS SERVER
32 22027 HENDRIKAB INACTIVE DEDICATED
33 55660 ALLASTAIRH INACTIVE DEDICATED
34 41856 JOHANS INACTIVE DEDICATED
38 43595 HENDRIKAB INACTIVE DEDICATED
49 56163 HENDRIKAB INACTIVE DEDICATED
58 28926 HENDRIKAB INACTIVE DEDICATED
65 9406 HENDRIKAB INACTIVE DEDICATED
79 47751 HENDRIKAB INACTIVE DEDICATED

Therefore, queries on v$session definitely doesn't decisively indicate stateless or statefull operation. As far as I can see, one would have to look at the config file, or maybe you'll have to look at v$session over time after changing to statefull and verify that the sid/serial#/USERNAME combination never change for the login in question, or maybe start more statefull connections than what is available in the pool (I haven't tested what happens in that case - our test environment is not online at the moment).

First of all, your two rules cannot be said often enough. Thanks for emphasizing them! (I am also surprised that we did not truly learn our lesson from y2k.)

Regarding date ranges: I too have had to deal with this type of question from colleagues. My answer varies slightly in that I feel uncomfortable in hard-coding the last possible moment in the day (1 second before midnight currently).

With the addition of timestamps in 9i, I can foresee where they will become universal in date-time usages, and I don't rule out the possibility that their granularity may find its way into date typed values (and shouldn't we all be encouraging code that avoids possible future problems?!).

Because of those reasons, I've always recommended the following approach for date ranges (to select inclusive dates from Jan 1st 2002 to Jan 17th 2002):

WHERE 1=1
AND dt_col BETWEEN TO_DATE('01-jan-2002','dd-mon-yyyy')
AND TO_DATE('17-jan-2002','dd-mon-yyyy')+1
AND dt_col <> TO_DATE('17-jan-2002','dd-mon-yyyy')

This to me is a universal definition of inclusiveness for dates (in the generic sense, including DATE and TIMESTAMP data formats).

It might be worth considering having a LAST_TIME built-in function developed, that returned the last possible date-time value for the date parameter passed, since this scenario is so prevalent (after all, LAST_DAY as a built-in is not really needed [it is the same as TRUNC (ADD_MONTHS(your_date,1) , 'MON')-1].

Thanks.

Followup to Bill's review.

This seems a bit excessive to me :

AND dt_col BETWEEN TO_DATE('01-jan-2002','dd-mon-yyyy')
AND TO_DATE('17-jan-2002','dd-mon-yyyy')+1
AND dt_col <> TO_DATE('17-jan-2002','dd-mon-yyyy')

I would have used :

AND dt_col >= TO_DATE('01-jan-2002','dd-mon-yyyy')
AND dt_col < TO_DATE('17-jan-2002','dd-mon-yyyy')+1


But since all you big brains seems to want to use BETWEEN - I wonder if there's a reason for that ?

Does the optimizer work better with BETWEEN than my way of doing it ?

Regards


Kim Berg Hansen
Senior Systems Engineer
T.Hansen Group
Denmark

For the example above :

where dt_col between to_date( '01-jan-2002 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
and to_date( '17-jan-2002 23:59:59', 'dd-mon-yyyy hh24:mi:ss' )


Can't we use TRUNC instead:
AND TRUNC(dt_col) >= TO_DATE('01-jan-2002','dd-mon-yyyy')
AND TRUNC(dt_col) < TO_DATE('17-jan-2002','dd-mon-yyyy')


Is this have any problem with Index.

Thanks,

shouldn't the statement

WHERE 1=1
AND dt_col BETWEEN TO_DATE('01-jan-2002','dd-mon-yyyy')
AND TO_DATE('17-jan-2002','dd-mon-yyyy')+1
AND dt_col <> TO_DATE('17-jan-2002','dd-mon-yyyy')

be

WHERE 1=1
AND dt_col BETWEEN TO_DATE('01-jan-2002','dd-mon-yyyy')
AND TO_DATE('17-jan-2002','dd-mon-yyyy')+1
AND dt_col <> TO_DATE('18-jan-2002','dd-mon-yyyy')

Yes, there was a typo in the sample code snippet posted; however, your fix, although accurate, would not be the syntax of choice. Introducing another data value (which of course would be actually handled by a host variable) is not efficient; all you want or need to have are the two dates signifying the beg/end you are interested in.

Here is an amended snippet:

WHERE 1=1
AND dt_col BETWEEN TO_DATE(:v_beg_dt,'dd-mon-yyyy')
AND TO_DATE(:v_end_dt,'dd-mon-yyyy')+1
AND dt_col <> TO_DATE(:v_end_dt,'dd-mon-yyyy')+1

-- where :v_beg_dt = '01-JAN-2002'
-- and :v_end_dt = '17-JAN-2002'
-- or some other values (perhaps parameter driven)

Taking others comments into account, I still think Oracle should provide an End-of-Day functionality, either with a LAST_TIME function or perhaps an EOD format suffix (adds last possible time component to supplied date).

Thanks for your comments.

Sir,

I have a query like..

SELECT A.DIV_CD,D.DIV_DESc
FROM TEST1 A,TEST2 B WHERE TO_DATE(RCIA_DT)=TO_DATE(:b1)AND ("SOME MORE STATEMENTS")

now because of TO_DATE(RCIA_DT)=TO_DATE(:b1) it's doing full table scan so how to avoid that full table scan??

Thanks.
atul

Sounds like RCIA_DT is a string then (else why would you to_date a date right)

So, if RCIA_DT is a string, just use "where rcia_dt = :b1 and ...."

QED

If rcia_dt is in fact a date, lose the to_date (cause you could easily corrupt a date by using to_date on it!)

Consider this (and think through WHY it is happening, hint, my NLS_DATE_FORMAT is dd-mon-rr)

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x date );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( to_date( '01-jan-1904', 'dd-mon-yyyy' ) );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select x, to_date(x), to_date(x) - x, to_char(to_date(x),'dd-mon-yyyy')
  2    from t;

X         TO_DATE(X TO_DATE(X)-X TO_CHAR(TO_
--------- --------- ------------ -----------
01-JAN-04 01-JAN-04        36525 01-jan-2004

ops$tkyte@ORA817DEV.US.ORACLE.COM> 


to_date(x) is not equal to x if X is a date in this example!!!

 

Tom:

if you have a html form where you prompt for an "effective date" and that is also part of the primary key in the table, oracle inserts the data with 00:00:00 timestamp which creates a problem in validating primary key.

SO I want to insert the date with the time the user entered with it:

Would you do insert into table(data) values
(to_Date(p_date)||to_char(sysdate,'hh24:mi:ss') )

OR you would add the time from 00:00:00 to current system time to the date.
How do you convert the current time to number and add it to date with 00:00:00 timestamp.

2. is there a way to update a timestamp for a date in the database?

Thank you,

I have a table which has column declared as varchar2.
Date is stored in that field. Why ? Its existing system so I
can't ask it.
Coming to my concern, Now the people have realised there mistake want to correct the data. So, is there a way I can get only the correct date fields and avoid the other records ?
here is sample data from the table :-
PK>select * from temp1;

ID
----------------------------------------------------------------------------------------------------
2002-01-01
2002-01-23
2002-13-01
2002-12-31
2002-12-34
200 -12-01
002-12 01
02- 1- 1
2002 01 01
2002- 4-01
2002-12- 9

11 rows selected.

Elapsed: 00:00:00.80

I have written something like this
PK>l
1 select to_date(id,'yyyy-mm-dd') from temp1
2 where length(id) = 10
3 and length(ltrim(rtrim(substr(id,1,4)))) = 4
4 and substr(id,5,1) = '-'
5 and substr(id,8,1) = '-'
6 and sign(substr(id,6,2) - 12) < 1
7* and sign(substr(id,9,2) - 31) < 1
PK>/

TO_DATE(I
---------
01-JAN-02
23-JAN-02
31-DEC-02
01-APR-02
09-DEC-02

5 rows selected.

Elapsed: 00:00:00.40
PK>

IS there a better way ?

Thanks
PK

Thanks for the immediate response.
But when I'm using the function I see some discrepancy :-
HEre is the data from the table
PK>select * from temp1;

ID
----------------------------------
2002-01-01
2002-01-23
2002-13-01
2002-12-31
2002-12-34
200 -12-01
002-12 01
02- 1- 1
2002 01 01
2002- 4-01
2002-12- 9
2003-10-10

12 rows selected.

Elapsed: 00:00:00.10

The blanks are null.

PK>l
1 CREATE OR REPLACE FUNCTION MY_to_Date
2 (v_inp_str IN VARCHAR2)
3 RETURN date IS
4 BEGIN
5
6 RETURN to_date(v_inp_str,'YYYY-MM-DD');
7 EXCEPTION
8 WHEN OTHERS THEN
9 return NULL ;
10* END;
PK>/

Function created.

Elapsed: 00:00:00.60
PK>

PK>select my_to_date( id ) from temp1;

MY_TO_DAT
---------
01-JAN-02
23-JAN-02

31-DEC-02

01-DEC-00
01-DEC-02
01-JAN-02
01-JAN-02
01-APR-02
09-DEC-02
10-OCT-03

12 rows selected.

Elapsed: 00:00:00.50
PK>


There are blank lines in between thats fine but I have a question regarding the number of columns returned .


PK>select to_char(my_to_date(id),'dd-mon-yyyy') from temp1;

TO_CHAR(MY_
-----------
01-jan-2002
23-jan-2002

31-dec-2002

01-dec-0200
01-dec-0002
01-jan-0002
01-jan-2002
01-apr-2002
09-dec-2002
10-oct-2003

12 rows selected.

Elapsed: 00:00:00.70

Why are the null spaces treated as zeros ??

Thanks in advance.

  1   select to_date ( hiredate,'dd-mon-yyyy:hh24-mi-ss') from emp
  2   where to_date(hiredate,'dd-mon-yyyy:hh24-mi-ss') +1  =
  3*  to_date('24-JAN-1982:12-00-00','dd-mon-yyyy:hh24-mi-ss')
SQL> /

no rows selected

  1   select to_date ( hiredate,'dd-mon-yyyy:hh24-mi-ss') from emp
  2   where to_date(hiredate,'dd-mon-yyyy:hh24-mi-ss') +1  like
  3*  to_date('24-JAN-1982:12-00-00','dd-mon-yyyy:hh24-mi-ss')
SQL> /

TO_DATE(H
---------
23-JAN-82 

Thank you Tom. I learn something daily from you

I did a SQL like this

select empleid, emple
from emp
where hiredate = to_date('05/03/2003','dd/mm/yyyy')

But there are not records... I have 10 records with that hiredate in emp table
I´m using Oracle 9i.... and the hiredate field is a date type

Hi Tom,
Do we have a function or way to format the date difference to "dd hh24:mi:ss" format? For example, after minus date2 from from date1, we get 0.25. Can we convert it into '6:00:00'?
Thank you for your help.

Tom

I changed the datatype of datetime columns in my batch_log table (BATCH_LOG.START_DATETIME and BATCH_LOG.END_DATETIME) from sysdate to systimestamp to get more detailed time information.

I used to be able to do

select batch_type
, avg(end_datetime-start_datetime)
, max(end_datetime-start_datetime)
, min(end_datetime-start_datetime)
group by batch_type

now with the systimestamp data type, I cannot do avg() or any aggregrate functions because it errors out

0RA-00932: INCONSISTENT DATATYPES: expected NUMBER got INTERVAL

In this case, is there a way to do interval arithmatic like I used to do?(does not seem to be supported in the manual)

Thanks so much!

Tom,
I need to get the past 12 months information (begining from current month), I used the following query, I felt not safety. Because the 2002 i_date might be 0002 or 2002 , so I can not get the correct result. Can you have a good idea ?


SELECT I_DATE FROM T
WHERE
TO_CHAR(ADD_MONTHS(sysdate,-11),'YYYYMM') < TO_CHAR(I_DATE,'YYYYMM') AND
TO_CHAR(I,'YYYYMM') <= TO_CHAR(sysdate,'YYYYMM');

Thank you!

Hi TOM,

I have a confusion regarding cmparing of time.

Say we have a table sdheduler with columns

locations varchar(20)
,batchstarttime date
,batchnumber number

and we have data like
LOCATIONS BATCHSTARTTIME BATCHNUMBER
-------- -------------- ------------
DO 01-JAN-2000 23:15 1
DO 01-JAN-2000 09:15 2
DO 01-JAN-2000 09:12 3
DO 30-DEC-1999 10:00 4
CO 01-JAN-2000 08:15 85
CO 01-JAN-2000 13:15 89
FO 30-DEC-1999 11:00 6
GO 30-DEC-1999 05:00 7
GO 30-DEC-1999 10:10 77
HO 30-DEC-1999 04:00 8
HO 30-DEC-1999 07:00 9
HO 30-DEC-1899 09:00 10
HO 01-JAN-2000 10:30 78

now i want to find out the batchnumber which ran just before 11:00 Hrs at any day for different locations

like from the above data i should get

DO 30-DEC-1999 10:00 4
CO 01-JAN-2000 08:15 85
GO 30-DEC-1999 10:10 7
HO 01-JAN-2000 10:30 78

How do i do this ?

i have been trying something like

SELECT a.locations, a.batchstarttime, a.batchnumber
FROM scheduler a
WHERE TO_CHAR (TO_DATE ('19-MAR-2004 11:00', 'DD-MON-YYYY hh24:mi'),
'hh24:mi'
) >
(SELECT MAX (TO_CHAR (b.batchstarttime, 'hh24:mi'))
FROM scheduler b
WHERE a.batchnumber = b.batchnumber AND a.LOCATION = b.locations)

how do i do this ? Please help.

Regards

you can use this technique (object_id = batch_number, object_name = locations, created = batchstartime

ops$tkyte@ORA9IR2> select object_id, object_name, to_char(created,'dd-mon-yyyy hh24:mi') created
  2    from (
  3  select object_id, object_name, max(created)
              over (partition by trunc(created)) max_created, created
  4    from all_objects
  5   where to_char( created, 'hh24' ) < '11'
  6         )
  7   where created = max_created
  8   order by created
  9  /
 
 OBJECT_ID OBJECT_NAME                    CREATED
---------- ------------------------------ -----------------
     30772 ALL_PARAMETERS                 01-dec-2003 10:21
     31367 TMP                            02-jan-2004 09:31
     34892 MYCTX                          11-feb-2004 09:15
     33923 T                              15-jan-2004 09:05
     34117 LOAD_TEST_DATA                 16-jan-2004 06:42
     35557 OP                             17-mar-2004 08:28
     35783 FTP                            18-mar-2004 10:57
     35797 EMP                            19-mar-2004 09:14
     34288 BONUS                          20-jan-2004 06:31
     34286 EMP                            20-jan-2004 06:31
     34289 SALGRADE                       20-jan-2004 06:31
     34290 DUMMY                          20-jan-2004 06:31
     34287 DEPT                           20-jan-2004 06:31
     30492 UTL_RECOMP_INV_IDX1            25-nov-2003 09:04
     34562 MY_FILES                       28-jan-2004 07:40
 
15 rows selected.


Or:

ops$tkyte@ORA9IR2> select object_id, object_name, to_char(created,'dd-mon-yyyy hh24:mi') created
  2    from all_objects x
  3   where to_char( created, 'hh24' ) < '11'
  4     and created = ( select max(created)
  5                       from all_objects
  6                                          where trunc(created) = trunc(x.created)
  7                                            and to_char(created,'hh24') < '11')
  8   order by created
  9  /
 
 OBJECT_ID OBJECT_NAME                    CREATED
---------- ------------------------------ -----------------
     30772 ALL_PARAMETERS                 01-dec-2003 10:21
     31367 TMP                            02-jan-2004 09:31
     34892 MYCTX                          11-feb-2004 09:15
     33923 T                              15-jan-2004 09:05
     34117 LOAD_TEST_DATA                 16-jan-2004 06:42
     35557 OP                             17-mar-2004 08:28
     35783 FTP                            18-mar-2004 10:57
     35797 EMP                            19-mar-2004 09:14
     34288 BONUS                          20-jan-2004 06:31
     34287 DEPT                           20-jan-2004 06:31
     34290 DUMMY                          20-jan-2004 06:31
     34286 EMP                            20-jan-2004 06:31
     34289 SALGRADE                       20-jan-2004 06:31
     30492 UTL_RECOMP_INV_IDX1            25-nov-2003 09:04
     34562 MY_FILES                       28-jan-2004 07:40
 
15 rows selected.


but you'll find the first one to be "better" i believe. 

sorry there was a typo in the above query....

SELECT a.locations, a.batchstarttime, a.batchnumber
FROM scheduler a
WHERE TO_CHAR (TO_DATE ('19-MAR-2004 11:00', 'DD-MON-YYYY hh24:mi'),
'hh24:mi'
) >
(SELECT MAX (TO_CHAR (b.batchstarttime, 'hh24:mi'))
FROM scheduler b
WHERE a.batchnumber = b.batchnumber AND a.locations = b.locations)

Please help me how can i install oracle 8i and 8 and forms in windows xp millions of thanks i need your responds thanks

Thanks a lot TOM....

a procedure where I can put something like this

01/01/2004 23:59:59

add 01/01/2004 00:00:01

and get 01/01/2004 24:00:01

and then 01/01/2004 00:00:00

and then get 01/01/2004 00:00:01

Thanks Tom

Sometime I get irrational demands. My manager thought
we could do the following

7/28/2004 9:00:00 AM display this
and then add 7/28/2004 9:00:01 AM
and see the result ???

Tom,

I have a query having one of the where condition as :
date_issued between to_date('01/01/2004','mm/dd/yyyy') and sysdate

I see a bad performance when I run the query with the above condition. Is there any other/better way to deal with dates in long running queries? I tried "and" instead of between and the performance was further bad. The best I could get was using between but wanted to check if any other way exists.

FYI, I have an index on date_issued column.

Thanks for all your help.

I even tried something like this:

select count(*) from (
<< here goes the fast running sql >>
)
where date_issued between to_date('01/01/2004','mm/dd/yyyy') and sysdate;

I thought since the subquery runs first and returns less than 200 rows, the date checking would be faster for 200 rows. But this too did not help.

How should I proceed??? :(

Tom, here is the trace plan that I ran, if at all it helps.

SQL> set autotrace on
SQL> SELECT count(*)
  2  FROM activity
  3  WHERE contact_id = 192271;

  COUNT(*)
----------
       129

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'IX_ACTIVITY_1' (NON-UNIQUE)(Cost=3 Card=30 Bytes=120)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        368  bytes sent via SQL*Net to client
        153  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT count(*)
  2  FROM activity
  3  WHERE contact_id = 192271
  4  and completed_date
  5    BETWEEN to_date('01-Mar-2004','dd-mon-yyyy')
  6    AND to_Date('30-Nov-2004','dd-mon-yyyy');

  COUNT(*)
----------
        50

1 row selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=11)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT)
   3    2       BITMAP AND
   4    3         BITMAP CONVERSION (FROM ROWIDS)
   5    4           INDEX (RANGE SCAN) OF 'IX_ACTIVITY_1' (NON-UNIQUE)(Cost=4)
   6    3         BITMAP CONVERSION (FROM ROWIDS)
   7    6           SORT (ORDER BY)
   8    7             INDEX (RANGE SCAN) OF 'IX_ACTIVITY_3' (NON-UNIQUE)(Cost=8)

Statistics
----------------------------------------------------------
      20570  recursive calls
       7623  db block gets
      14380  consistent gets
      26775  physical reads
     479136  redo size
        368  bytes sent via SQL*Net to client
        445  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          1  sorts (disk)
          1  rows processed

SQL>  

Regarding the previous post ...

The ACTIVITY table probably has a bitmap index on it (likely on something other than ACTIVITY_ID or COMPLETED_DATE) ... if this the case (and this is an OLTP environment) consider removing it or replace it with a regular index. Goes w/o saying that stats should be up-to-date regardless.

Tom:

I have a table that includes two fields:

Invoice_Month Varchar2(10),
Invoice_FY number(4)

First field is the month the invoice was for, and the second is the year the month the invoice for.

I need to create a report that allows a user to report on records for a given date range like (Jan 2003 - March 2004).

1. Can I write a SQL statement that queries the string using "between" like
"give me all records where invoice_month between "jan" and "marc" and invoice_fy between "2003" and "2004"?

2. If you were designing this would you change the type of Invoice_month to a number and a do range check on the number?



Thank you,

Tom:

Well the requirement was to track the month and year for what the invoice was for.

You can receive an invoice dated "05-DEC-2004" where this is stored in invoice_date. However the person sending the invoice has to specify that this invoice was for work completed in the month of "June" and "2004" Fiscal Year.

However, I think storing a number for the month and year would solve this problem. Then we can do

select * from table where invoice_month between 1 and 3 and invoice_fy between 2004 and 2004.

Do you see any potential problems with that?

Tom:

I am confused. What is the problem with this solution?


Invoice_Month Number(2),
Invoice_FY number(4),
Invoice_date date,


Query: What invoices have been billed for January 2003 and March 2004?

Answer:

select * from table where invoice_month between 1 and 3 and invoice_fy between
2003 and 2004.


Please note invoice date is the actual date on the invoice but it could be for some work completed 6 months ago.

Hi Tom,
I thought you'd want to know that there's a bug in the sample query in </code> http://asktom.oracle.com/~tkyte/Misc/DateDiff.html <code>
In
select to_char( created, 'dd-mon-yyyy hh24:mi:ss' ),
trunc( sysdate-created ) "Dy",
trunc( mod( (sysdate-created)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-created)*24*60, 60 ) ) "Mi",
trunc( mod( (d1-d2)*24*60*60, 60 ) ) "Sec",
to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
sysdate-created "Tdy",
(sysdate-created)*24 "Thr",
(sysdate-created)*24*60 "Tmi",
(sysdate-created)*24*60*60 "Tsec"
from all_users
where rownum < 50
/

the line
trunc( mod( (d1-d2)*24*60*60, 60 ) ) "Sec",
should probably read
trunc( mod( (sysdate-created)*24*60*60, 60 ) ) "Sec",
.

Cheers,
Flado

Tom:

You are very correct. I combined the two fields into one field of type date and I am storing the first day of the month as part of the date.

Thank you,

Hi Tom,

I have a problem in some code that is not returning the information it should and I'm not sure why.  But I almost certain the developer who wrote this code didn't compare the dates correctly, here's a the sample:

SQL> CREATE TABLE t (comp_date DATE, file_num VARCHAR(13), id_num NUMBER, comp_oid NUMBER);

Table created.

SQL> insert into t values(to_date('6/1/2005', 'MM/DD/YYYY'), null, '1', '50');

1 row created.

SQL> SELECT comp_oid
  2    FROM t
  3   WHERE NVL (comp_date, TO_DATE ('01/01/1900', 'MM/DD/YYYY')) =
  4                          TO_DATE (NVL ('6/1/2005', '01/01/1900'), 'MM/DD/YYYY')
  5     AND file_num = NULL
  6     AND id_num = '1';

no rows selected

SQL> show parameters nls_date_format;

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
nls_date_format                      string
SQL>

The date values never equal so I don't get any results.  I think they wrote that code to standardize the date for a basis of comparison. 

So the fact that I hardcoded values merly for a test can cause inacurate results?  That doesn't sound right.

Also, to clarify, if I have a line like this in a WHERE clause:

WHERE file_num = pfile_num 

and pfile_num is a parameter coming in that is null, this query will never return anything?  

I'm trying this at the moment but I get an error but I was wondering if this going in the direction you suggested:

SQL> DECLARE
  2     v_comp_date   DATE         := TO_DATE ('6/1/2005', 'MM/DD/YYYY');
  3     v_file_num    VARCHAR (13) := NULL;
  4     v_id_num      NUMBER       := '1';
  5     v_comp_oid    NUMBER;
  6  BEGIN
  7     SELECT comp_oid
  8       INTO v_comp_oid
  9       FROM t
 10      WHERE comp_date = TO_DATE (v_comp_date, 'MM/DD/YYYY')
 11        AND file_num = v_file_num
 12        AND id_num = v_id_num;
 13  EXCEPTION
 14     WHEN NO_DATA_FOUND
 15     THEN
 16        DBMS_OUTPUT.put_line ('Still got no rows');
 17  END;
 18  /
DECLARE
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 7

It works in TOAD though, but I know you don't use that. 

Got it to work thanks. I am curious about one other thing when you said

c) WHERE NVL (comp_date, TO_DATE ('01/01/1900', 'MM/DD/YYYY')) =
TO_DATE (NVL ('6/1/2005', '01/01/1900'), 'MM/DD/YYYY')

should only be

where comp_date = to_date(:x,'mm/dd/yyyy')

I don't see how those can be functionally equivalent

Let a table--
my_dates(prev_processing_date date, last_dml_date date);
I need a trigger for this ----
After DML on any table of my_user
the DML date will be inserted in the above table
and
the last stored date from another table needs to be queried and inserted into the above table.
Example :
ROWID PREV_PROCESSING_DATE ---- LAST_DML_DATE
1 12-MAY-05 ----------- 15-MAY-05
2 16-MAY-05 ------------

//DATA OF ROWID 1 MEANS THAT AFTER EXECUTING PROCEDURE THERE IS SOME DML
//DATA OF ROWID 2 MEANS THAT AFTER EXECUTING PROCEDURE THERE IS NO DML
Is it possible??

Tom,

You mentioned the implicit conversions going on a couple posts up. I've been trying to do a little research on this, I want to be able to see how this effects queries. Is there somewhere where this is covered in some detail? I have tried the docs, they were a little helpful. I've been unable to locate anything in your books on this (though I swear I've read about it in there somewhere). I tried a small test myself and saw no difference in stats.

Tom,

Could you clarify if Oracle updates is date/time based on Operating system date? Could we set Oracle date/time thats not dependent on O/S date/time and is it possible?

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for HPUX: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> select * from nls_database_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                RRRR/MM/DD HH24:MI:SS

SQL> select * from nls_instance_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                RRRR/MM/DD HH24:MI:SS

SQL> select XBO_Gsf_time_togmt(to_date('2005/11/21 16:48:56'),240) from dual;
select XBO_Gsf_time_togmt(to_date('2005/11/21 16:48:56'),240) from dual
                                  *
ERROR at line 1:
ORA-01861: literal does not match format string

Function:
CREATE OR REPLACE Function xbo_Gsf_time_togmt(i_date    in  date,
    i_offset    in  number)
    Return date
is
    l_date      date;
Begin
        if i_date is NULL then
                return(NULL);
        end if;
      l_date := i_date + (i_offset/1440);
    return(l_date);
EXCEPTION
            when invalid_Number then
                       return( NULL );
            when others
                    then    raise;
end  xbo_Gsf_time_togmt;
/

Why the imlicit date conversion is not taking place? Please explain
 

because the only one that counts is.... your SESSION:

ops$tkyte@ORA10GR2> select * from nls_database_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

ops$tkyte@ORA10GR2> select * from nls_instance_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT

ops$tkyte@ORA10GR2> select * from nls_session_parameters where parameter='NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                dd/mm/yyyy

ops$tkyte@ORA10GR2>



(hint: avoid implict like the plague, explicit is good) 

Tom, Thanks for the reply. Always I go by explicit conversion but this is existing code in production.


1. Can I able to get the default date format used by oracle for implicit conversion without quering nls_session_parameters. I enabled the trace and tried to find out about default date format used. But I am not able to find date format used for implicit conversion in trace file.

You may surprise why I need it. I will explain in detail below,

Oracle version: 9.2.0.6
JDBC driver version :9.2.0.5

We are calling the below procedure from Java which make use of xbo_Gsf_time_togmt (my previous thread) function. In Java we are not setting any date format. We set the instance and database date format as RRRR/MM/DD HH24:MI:SS. Procedure ran successfully.

Now we updated the JDBC driver to 10.2.0.1 version. Database is still in 9.2.0.6 version.
Same procedure throws "ORA-01861: literal does not match format string" error.

If the trace gives the default date format used while doing implicit date conversion it will be useful. Anyway our dba is contacting oracle support for this JDBC driver issue.

CREATE TABLE XBO_ET_EQUIP
(
LOCATION VARCHAR2(20),
EQUIP_ID VARCHAR2(60),
STATUS CHAR(1),
USER_ID VARCHAR2(30),
LAST_UPDT_DATE DATE,
DUE_DATE DATE,
COMP_DATE DATE
);


INSERT INTO XBO_ET_EQUIP ( LOCATION, EQUIP_ID, STATUS, USER_ID, LAST_UPDT_DATE, DUE_DATE,
COMP_DATE ) VALUES (
'SR74NJ05H00', 'SR74-WHHS3H00', 'I', 'direct', sysdate,sysdate,sysdate-1);
COMMIT;

CREATE OR REPLACE procedure xbo_esp_equip_status(
i_LOCATION in VARCHAR2,
i_EQUIP_ID in VARCHAR2,
i_status in char,
i_comp_DATE in varchar2,
i_USER_ID in VARCHAR2,
i_offset in number
)
as
begin
update xbo_et_equip
set STATUS = i_status,
comp_DATE = DECODE(i_comp_DATE,'?',comp_DATE,XBO_Gsf_time_togmt(i_comp_DATE,i_offset)),
USER_ID = i_USER_ID,
last_updt_date = sysdate + (XBO_GSF_TIME_OFFSET/(24*60))
where location = i_location
and equip_id = i_equip_id
;

EXCEPTION
when others
then raise;
end xbo_esp_equip_status;
/

Thanks a lot Tom.

I suggested the same to our dba and other decision making people.

</code> http://asktom.oracle.com/~tkyte/Misc/NLSDateFormat.html <code>
I gone through this link, typically it is happening in Windows client but we are using both client and server as unix.

Can I able to get the default date format used by oracle for implicit conversion without quering nls_session_parameters using any oracle trace.

Thanks a lot Tom.

Our DBA people accepted to change the procedure for explicit conversion.

Thank you very much this qury was very helpful to me in describing how to query a single dable by created_dt
Thanks again

Hi Tom,
good day to you as always, I need your help on a query I have, I have table in which records for customers are there having different products I need to fetch customers having just two products as below
e.g Table T (customer,product)
1,PRD-3-2
1,PRD-7-1
2,PRD-6-1
2,PRD-3-2
3,PRD-7-1
3,PRD-6-1

I want customer with PRD LIKE PRD-3% AND PRD-6% but no other products in the above e.g. customer 2

In my htmldb application, my column for start_date was displaying wrong dates when the format was in yyyy/mm/dd.

I had wrongly typed to_date( start_date, 'yyyy/mm/dd') instead of to_char( start_date, 'yyyy/mm/dd' ).

I checked in sql prompt but could not understand why to_date (date column with different format string) gives an unexpected answer; (maybe it is the expected answer, but I couldn't understand why).

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY/MM/DD'), TO_DATE(SYSDATE, 'YYYY/MM/DD'), TO_DATE(SYSDATE, 'RRRR/MM/DD') FROM DUAL ;

1/20/2006 3:33:57 PM 2006/01/20 1/6/0020 1/6/2020

My question: Why is it displaying 2020 or (0020)? Is it really the century part ?

When I try to do

select to_date( to_date('20/01/2006', 'dd/mm/yyyy'), 'RRRR/MM/DD') FROM DUAL ;

it gives an error ora-1858 error (non-numeric character was found where a numeric was expected) what numeric value is expected here ?

Hi Tom,
thanks for the answer to my question on PRD LIKE PRD-3% AND PRD-6% and sorry for the data was not sufficient enough for you to understand my question, below i give the data and my question again.

Table T (customer, product) A customer can have multiple products subscribed products have id has PRD-3-1 now the first digit will change and can be 3,6,7 and will not be a double digit I want to retrieve customers having just products with product id as PRD-3% AND PRD-6% i.e all customers having subscribed to just these two product family not the product family of PRD-7.....
for e.g. the data as follows

101 PRD-3-1
101 PRD-6-2
102 PRD-3-1
102 PRD-7-1
103 PRD-3-1
104 PRD-6-1
105 PRD-3-1
105 PRD-7-1
106 PRD-6-1
106 PRD-7-1

the query should return just 101 customer as this customer has only these two produts to his account, please if you can help me on this.

Kind Regards,
Vijay

Hi Tom
For some reason i dont understand why i cant display the data in the follwing way:
QTY INSERT_
---------- -------
35 03/2005
43 04/2005
25 05/2005
17 06/2005
20 07/2005
53 08/2005
31 09/2005
38 10/2005
45 11/2005
72 12/2005
76 01/2006
4 02/2006

Would you please explain why ?

Hir is some data:


create table t_test
(qty number,
insert_date date);

insert into t_test values(76, '01-jan-2006');
insert into t_test values(36, '01-feb-2005');
insert into t_test values(4 , '01-feb-2006');
insert into t_test values(35, '01-mar-2005');
insert into t_test values(43, '01-apr-2005');
insert into t_test values(25, '01-may-2005');
insert into t_test values(17, '01-jun-2005');
insert into t_test values(20, '01-jul-2005');
insert into t_test values(53, '01-aug-2005');
insert into t_test values(31, '01-sep-2005');
insert into t_test values(38, '01-oct-2005');
insert into t_test values(45, '01-nov-2005');
insert into t_test values(72, '01-dec-2005');
commit;

select * from t_test;
QTY INSERT_DA
---------- ---------
76 01-JAN-06
36 01-FEB-05
4 01-FEB-06
35 01-MAR-05
43 01-APR-05
25 01-MAY-05
17 01-JUN-05
20 01-JUL-05
53 01-AUG-05
31 01-SEP-05
38 01-OCT-05
45 01-NOV-05
72 01-DEC-05

13 rows selected.

I need to display the data in the following way:
QTY INSERT_
---------- -------
35 03/2005
43 04/2005
25 05/2005
17 06/2005
20 07/2005
53 08/2005
31 09/2005
38 10/2005
45 11/2005
72 12/2005
76 01/2006
4 02/2006

This is the best i could get: (I belive i dont need to use to_char)

select sum(qty) ,to_char(insert_date,'mm/yyyy') insert_date
from t_test
where insert_date between sysdate - 360 and sysdate
group by to_char(insert_date,'mm/yyyy')
order by 2

SUM(QTY) INSERT_
---------- -------
76 01/2006
4 02/2006
35 03/2005
43 04/2005
25 05/2005
17 06/2005
20 07/2005
53 08/2005
31 09/2005
38 10/2005
45 11/2005
72 12/2005

12 rows selected.

ops$tkyte@ORA9IR2> select qty, to_char(insert_date,'mm/yyyy')
  2    from t_test
  3    where   insert_date between sysdate - 360 and sysdate
  4   order by insert_date;

       QTY TO_CHAR
---------- -------
        35 03/2005
        43 04/2005
        25 05/2005
        17 06/2005
        20 07/2005
        53 08/2005
        31 09/2005
        38 10/2005
        45 11/2005
        72 12/2005
        76 01/2006
         4 02/2006

12 rows selected.
 

I want to take several date/time interval values and sum them up.  Do
I REALLY need define my own special type using ODCIAggregateIterate()
as you do above??  Why doesn't Oracle's sum() function "just work" on
native Oracle interval types?

And since sum() doesn't work on interval types, shouldn't there be
some easy way to cast an interval type to a plain floating point
number measured in seconds, so I can sum up those elapsed times?  Yet
cast() doesn't seem to work either.

Is there any robust PL/SQL package available to provide aggregate
functions on interval types?


Some distressing examples:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select  1 as id  ,(interval '0 00:00:01' day to second) as sec  from all_tables  where rownum <= 3;

        ID SEC
---------- --------------------
         1 +00 00:00:01.000000
         1 +00 00:00:01.000000
         1 +00 00:00:01.000000

SQL> select id ,sum(sec)  from (
select  1 as id  ,(interval '0 00:00:01' day to second) as sec  from all_tables  where rownum <= 3
) group by id ;
  2    3  select id ,sum(sec)  from (
               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SQL> select  1 as id
  ,cast((interval '0 00:00:01' day to second) as number) as sec
from all_tables  where rownum <= 3 ;
  2    3    ,cast((interval '0 00:00:01' day to second) as number) as sec
         *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

SQL> select  1 as id
  ,cast((interval '0 00:00:01' day to second) as date) as sec
from all_tables  where rownum <= 3 ;
  2    3    ,cast((interval '0 00:00:01' day to second) as date) as sec
         *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
 

Here's a workaround:

SQL> select id, sum(sysdate+sec-sysdate)*86400 
  2  from 
  3  (select  1 as id  ,(interval '0 00:00:01' day to second) as sec  from 
  4  all_tables  where rownum <= 3)
  5  group by id;
        ID SUM(SYSDATE+SEC-SYSDATE)*86400
---------- ------------------------------
         1                              3

1 row selected.

or to avoid multiple calls to sysdate:

SQL> select id, sum(now+sec-now)*86400
  2  from 
  3  (select  1 as id  ,(interval '0 00:00:01' day to second) as sec  from 
  4  all_tables  where rownum <= 3),
  5  (select sysdate now from dual where rownum<2)
  6  group by id;
        ID SUM(NOW+SEC-NOW)*86400
---------- ----------------------
         1                      3

1 row selected.

Regards
Michel 

Hi Tom,

Regard to the questsion i asked in February 02, 2006 .
I add the following 3 insert:

insert into t_test values(2, '05-jan-2006');
insert into t_test values(3, '06-feb-2005');
insert into t_test values(6 , '07-feb-2006');

Then i query the the table again:
select * from t_test;

QTY INSERT_DA
---------- ---------
76 01-JAN-06
36 01-FEB-05
4 01-FEB-06
35 01-MAR-05
43 01-APR-05
25 01-MAY-05
17 01-JUN-05
20 01-JUL-05
53 01-AUG-05
31 01-SEP-05
38 01-OCT-05
45 01-NOV-05
72 01-DEC-05

Since i want to aggregate the data per month , i used the SUM function

select sum(qty) ,to_char(insert_date,'mm/yyyy') insert_date
from t_test
where insert_date between sysdate - 360 and sysdate
group by to_char(insert_date,'mm/yyyy')
order by 2;

SUM(QTY) INSERT_
---------- -------
76 01/2006
4 02/2006
35 03/2005
43 04/2005
25 05/2005
17 06/2005
20 07/2005
53 08/2005
31 09/2005
38 10/2005
45 11/2005
72 12/2005

12 rows selected.

The problem is that data is not sorted the way i wanted.
You solution wasnt good for me because you didnt used SUM.

Thanks Again.

Hi Tom,

We have solaris server.
when I try to find out sysdate using following query it shows:
SQL> select to_char(sysdate,'DD-MM-YYYY HH24:MM:SS') "Date" from dual;

Date
-------------------
21-02-2006 10:02:26

Immediately after this I am using "date" command to find out system date. It shos:

SQL> !date
Tue Feb 21 10:19:38 IST 2006

Why these two date are different? Does 'dual' not take date from system date?
 

Hi Tom,

I found out the below sql in V$sqlarea.

>>select TRUNC(SYSDATE@!) from dual
2 /

TRUNC(SYSDATE@!
---------------
25-MAY-06

>>

Is there any significance of puting "@!" after sysdate ??

Thanks
Tanmoy

Oracle 9.2.0.7 Solaris, accessed by web client 9.2.0.7 Windows

In Where clause:
OA.CREATE_DATE >= TO_DATE('8/7/06', 'MM/DD/RR') AND
OA.CREATE_DATE < (TO_DATE('8/8/06', 'MM/DD/RR') +1)

results in Cost 15000 in explain plan full table scans
changing to full 4 character year format reduces cost by factor of 15

In Where clause :
OA.CREATE_DATE >= TO_DATE('8/7/2006', 'MM/DD/YYYY') AND
OA.CREATE_DATE < (TO_DATE('8/8/2006', 'MM/DD/YYYY') +1)

Cost 1000 in explain plan index range scans

OA.CREATE_DATE is a DATE datatype default sysdate
NLS_DATE_FORMAT is DD-MON-RR

My apologies if this seems naive.

I notice a lot of queries in this and other articles about Oracle date data types involve common business questions in which the time portion is not crucial (invoices from January or items shipped on 15-JUN-2006). I borrowed the Apex date picker approach, which TRUNCs the date so you get an easy-to-use DD-MON-YYYY date that makes it accurate to query in the form of:

where invoice_date = to_date(:P2_INVOICE_DATE,'DD-MON-YYYY')

instead of

where invoice_date >= to_date(:P2_INVOICE_DATE,'DD-MON-YYYY') and
invoice_date < to_date(:P2_INVOICE_DATE,'DD-MON-YYYY')+1

Is there anything wrong with creating a trigger on the table to truncate the date? Something like:

CREATE OR REPLACE TRIGGER trunc_invoice_trg
BEFORE INSERT OR UPDATE
ON invoice
FOR EACH ROW
BEGIN
:NEW.invoice_date := TRUNC(:NEW.invoice_date);
END;

This should take care of any inserts or updates using SYSDATE as well as preserving the usefulness of indexes (assuming you don't use function based indexes).

Tom,

I have a situation where in the database server is in EST time zone with DATE fields. The date fields are read by a ¿C/C++¿ program and sent over in an xml file to a ¿C/C++¿ program running on DB servers in deferent time zone. For an example a DB server running in Singapore. The remote program needs to insert the date/time into the Singapore database so that their clients see the time in their own time zone.

So the field needs to be stored in Singapore database but with a date/time that is the exact Singapore conversion date of EST date when it was originally created in the EST time zone.

For example, the field was created on Feb 15th 8:00PM which will be Singapore time Feb 16th in the morning. We want to store this converted date/time in the Singapore database.
Could you guide me what could be a the best route to do it and how to do it? meaning, do we have to use TIMEZONE and then use the NEW_TIME and FROM_TZ etc to convert the date? Or maybe altering the database field in the Eastern Time zone to a TIMESTAMP with zone is better (not sure if this is an option for me yet)?

Thanks.
Phillip

Hi Tom,

Do you have any feedback/recommendation on my original post just above there?

Thanks
Phillip

Hi Tom,

I am receiving a varchar2 field in the format yyyymmddhhmissSSS where hh is in 24 hour format and SSS is in 3 millisecond digits.

I want to store this fields value in a timestamp format into the database.How to go about it?

I have tried following things but without success.

To_Timestamp('20060101010112345','yyyymmddhh24:mi:ss.ff')
To_Timestamp('20060101010112345','yyyymmddhh24:mi:ss.fff')
To_Timestamp('20060101010112345','yyyymmddhh24:mi:ss.ff3')

Thanks in advance.

Regards,
Sourabh S Jain

ops$tkyte%ORA10GR2> select
  2  To_Timestamp('20060101010112345','yyyymmddhh24missff')
  3  from dual;

TO_TIMESTAMP('20060101010112345','YYYYMMDDHH24MISSFF')
---------------------------------------------------------------------------
01-JAN-06 01.01.12.345000000 AM

Hi Tom,
When I run a query on a log table like the following:
select to_char(EVENT_DATE, 'YYYY-MM-DD') evt_d1,
to_char(EVENT_DATE, 'YYYY-Mon-DD HH24:MI:SS') evt_d2 from evet_logs
where event_date >= to_date('2007-02-28','YYYY-MM-DD')
and event_date < to_date('2007-03-01','YYYY-MM-DD')
order by evt_d1;

I got results as below. Why Some of the dates are shown as Feb. 29, 30, 31, which do not exist at all? What could be the cause and how can I correct this? Or is there anything wrong with my date format?
Thank you for your help.

EVT_D1 EVT_D2
-------------------- --------------------
2007-02-31 2007-Feb-31 14:55:16
2007-02-31 2007-Feb-31 20:22:45
2007-02-31 2007-Feb-31 20:59:12
2007-02-31 2007-Feb-31 22:44:28
2007-02-31 2007-Feb-31 23:00:50
2007-02-31 2007-Feb-31 22:40:13
2007-02-31 2007-Feb-31 22:30:12
2007-02-31 2007-Feb-31 23:18:43
2007-02-30 2007-Feb-30 16:07:22
2007-02-30 2007-Feb-30 17:00:23
2007-02-29 2007-Feb-29 13:38:29
2007-02-29 2007-Feb-29 16:31:52
2007-02-29 2007-Feb-29 18:35:19
2007-02-29 2007-Feb-29 19:25:23
2007-02-28 2007-Feb-28 00:00:00

Hi Tom,
The below query is not working.
what can be the reason?

SQL> select sysdate from dual
2 /

SYSDATE
---------
27-APR-07

1 row selected.

SQL> select current_date from dual
2 /

CURRENT_D
---------
27-APR-07

1 row selected.

SQL> create table t(x int)
2 /

Table created.

SQL> select * from obj where created = sysdate
2 /

no rows selected

SQL> select * from obj where created = current_date
2 /

no rows selected

SQL> select * from obj where created = (select sysdate from dual)
2 /
no rows selected

Thanks Tom.
But I am not able to get the result
even when I use the trunc(sysdate) function which removes the time part from sysdate.
How to get the result for this query?

Hi Tom,
Just a curiosity.
Won't we able to find from data dictionary
what are the tables that have been created on
today or two days back etc. by passing input as sysdate
or sysdate-2 ?
Please let me know how we can do this.

Hi Tom,

We need to code a PLSQL function to check if the date format of an input variable of type varchar2 is 'YYYY-MM-DD' or not.

We just thought that we will write this query which if doesn't throw any exception indicates that it is indeed in the desired format.

SELECT TO_DATE(input_variable,'YYYY-MM-DD') FROM dual;

Yes, we know that this has a catch inherently, since if the input is '2007-05-12', then we don't know if the input format is actually 'YYYY-MM-DD' or 'YYYY-DD-MM'.

However, when we run the below query (on PLSQL Developer), we get strange output:

SELECT TO_DATE('04-May-07','YYYY-MM-DD') FROM dual;

Output:

07/05/0004

Why is this so? How do we do what we want to?

Thanks.

We have a requirement in which user can input some information for a date. Inormation for date will remain same for that date, user can update it but will not have multiple enteries for the same date.

There will be some information which will have multiple enteries in a date. For these multiple enteries within a date user will iput time like (08:20,15:30 etc). There will be two text boxes on input form one for date and one for time. So we will be keeping to columns in table one with date data type and one with varchar2. You suggest mostly that all such information should be stored in date. How we can store this information in date as from front end we will be getting only time portion form user. User will not ready to input date with time on every repeating entry.

when i try to run the below block i get less as output why the comparison using the to_char and string is not correct. i guess both will be using ascii internally ?

begin
  if to_char(sysdate,'mm/dd/yyyy') < '12/29/2005' then
  
     dbms_output.put_line ( ' less ' );
  else
    dbms_output.put_line ( ' Not less ' );
   end if;
   
end;

output is

less

ops$tkyte%ORA10GR2> begin
  2    if to_char( last_day( add_months(trunc(sysdate,'y'),11) ),'mm/dd/yyyy') < '12/29/2005' then
  3       dbms_output.put_line ( ' less ' );
  4    else
  5      dbms_output.put_line ( ' Not less ' );
  6     end if;
  7  end;
  8  /
Not less

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select to_char( last_day( add_months(trunc(sysdate,'y'),11) ), 'mm/dd/yyyy') from dual;

TO_CHAR(LA
----------
12/31/2007

In one of your replies regarding ORa 1858
"
this is the wrong way:

v_id_num NUMBER := '1';

please -- numbers to numbers, dates to dates, strings to strings and don't do it any other way!

v_id_num number := 1;



this is *really* wrong:

WHERE comp_date = TO_DATE (v_comp_date, 'MM/DD/YYYY')

v_comp_date is a date!

that is really = to_date( TO_CHAR(v_comp_date), 'MM/DD/YYYY' )
"

What is wrong with converting a data column or variable again to a date using to_date function ?

What will be the conversion format/mask for the implicit conversion TO_CHAR(v_comp_date) from date to char field ?

anto

ops$tkyte%ORA11GR1> alter session set nls_date_format = 'DD/MM/YYYY';

Session altered.

ops$tkyte%ORA11GR1> create table t as select to_date( '12-jan-2008', 'dd-mon-yyyy' ) x from dual;

Table created.

ops$tkyte%ORA11GR1> select x, to_date( x, 'mm/dd/yyyy' ) from t;

X          TO_DATE(X,
---------- ----------
12/01/2008 01/12/2008

Thanks Tom - just as I had thought

dagstg@STGDEV1> alter session set nls_date_format = 'dd-mon-yyyy';

Session altered.

dagstg@STGDEV1> create table t as select to_date( '12-jan-2008', 'dd-mon-yyyy' ) x from dual;

Table created.

dagstg@STGDEV1> select x, to_date( x, 'mm/dd/yyyy' ) from t;
select x, to_date( x, 'mm/dd/yyyy' ) from t
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


dagstg@STGDEV1> edit
Wrote file afiedt.buf

1* select x, to_date( to_char(x,'mm/dd/yyyy'), 'mm/dd/yyyy' ) from t
dagstg@STGDEV1> /

X TO_DATE(TO_
----------- -----------
12-jan-2008 12-jan-2008

Since the to_char conversion mask and to_date conversion mask do not match we get this 1858. So it is better to avoid to_date to date columns/variables

Just wanted to make sure that the implicit to_char was done using the session/db level nls_date_format mask

thanks
Anto

Hi Tom

Thanks for your time.

Recently while running a query involving date columns we got hit by ORA-07445, we raised the TAR with Oracle and got the resolution as ( Our RDBMS ver 9.2.0.6) :

1.- upgrade to 9.2.0.7 where this bug is solved (but recommend you to upgrade to 9.2.0.8 as this is the final patchset for 9.2 release)
2.- use the following workaround :
alter system set events '38055 trace name context forever, level 1';
alter system set events '10078 trace name context forever, level 1';
alter system flush shared_pool;
3. using TO_DATE with full date format mask for the query :
select count(*) from T where creation_time > '01-MAR-08 00:00:00' and creation_time < '23-JUL-08 00:00:00'

We are yet to try workaround 2 above. Can you please explain by an example thet how by setting trace events:

event 38055 level 1 (disable redundant range predicate removal)
event 10078 level 1 (disable removal of constant predicates)

will disable redundant range predicate /removal of constant predicates.

I hope you will take my question, as i wanted to know "how" setting above trace events will help us.

I understand that user should have used date to date comparision in above query and would have not left it to Oracle to do the implicit conversion.

Kind regards

Hi Tom


...I would go for option #3 myself - it is the right way to do it - never rely on implicit date formats - never.

You are right. option#3 is best. But that you and me will use but it is very difficult to tell the people to use explicit conversion.

So, we are opting option#2 as well. Because going for option#1 upgrade is not immediately possible.

Does it means that if we use option#2 and *our people* dont use explicit conversion, then we wont get hit by ORA-07445?

Regards

Many Thanks Tom for your valuable info.

...
>>but it is very
difficult to tell the people to use explicit conversion.
....

>why? Is it hard to tell them to "design"? to do things "right" or "correctly"? To do something obviously intelligent? smart? safe?

I don't get it....
....

Sorry for not making it clear earlier. You are right that *design* people should do the things "right" or "correctly".

But here *other* people i was refrering to are end users who use SQLPLUS/SQLDeveloper to get the results from the database and thus use their own set of sqls wherein they not use explicit conversion. Though developer would have followed the things to take care date conversion in design.

We got hit by ORA-07445 because one of the *other people* tried to execute the SQL query involving date cols without explicit conversion.

Further,

....If support says "those two events will work around this issue", it is likely 'true' - however, if you look at those events, they disable features of the optimizer - obviously you should be expecting immediate side effects as this effects everything in the entire system, right away.

What should be apparoach to proceed on setting "those two events" then.

We have tested the same in our test environment and ran the same query. Found no problem , so now decided to implement the same in Live.

We have CBO with MODE First_Rows

Regards.

Hello Tom,

I'm stuck with legacy code full of implicit conversions between character types and numbers. That made me investigate rules of implicit conversions by testing and reading conversion matrix in the SQL reference (never needed before, since I use explictit conversion all the time).

I'd like to ask for the reason of Oracle always converting from char to number. I want to understand why Oracle chooses this "unsafe" direction.

You can't convert "Hello world!" to a number. But you can convert whatever number to a string. The only reason which I can think of for not doing it is that converting number to string may not be precise (binary vs decimal issues), but I am not sure whether this is why Oracle doesn't do it.

ps. Enjoy your near stay in our city

0
0.0
.0
0e1
000.0000000
 0
+0
-0

Thank you,

I haven't thought it thoroughly enough, have to try harder next time :)

Hi Tom

thought i had a handle on the date formatting using BETWEEN
but am missing something. I cannot figure out why I am not getting all my data when using the BETWEEN statement with a date in one particular sql and not the other

Thanks

**************************************************

create table date1
(
col_date date
);


insert into date1 values (to_date('2009/01/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/02/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/03/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/04/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/05/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/06/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/07/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/08/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/09/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/10/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/11/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/12/01','yyyy/mm/dd'));

insert into date1 values (to_date('2009/12/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/12/01','yyyy/mm/dd'));
insert into date1 values (to_date('2009/12/02','yyyy/mm/dd'));
insert into date1 values (to_date('2009/12/02','yyyy/mm/dd'));
insert into date1 values (to_date('2009/12/03','yyyy/mm/dd'));
insert into date1 values (to_date('2009/12/03','yyyy/mm/dd'));

commit;

compute sum of date_count on report
break on col_date on report

================================================================
--yyyy/mm/dd test

select
to_char (col_date,'yyyy/mm/dd') col_date,
count (col_date) date_count
from date1
where col_date BETWEEN TO_DATE ('2009/01/01','yyyy/mm/dd') and TO_DATE ('2009/12/31','yyyy/mm/dd')
group by to_char (col_date,'yyyy/mm/dd')
order by 1;

output:
COL_DATE DATE_COUNT
---------- ----------
2009/01/01 1
2009/02/01 1
2009/03/01 1
2009/04/01 1
2009/05/01 1
2009/06/01 1
2009/07/01 1
2009/08/01 1
2009/09/01 1
2009/10/01 1
2009/11/01 1
2009/12/01 3
2009/12/02 2
2009/12/03 2
********** ----------
sum 18


--yyyy/mm test

select
to_char (col_date,'yyyy/mm') col_date,
count (col_date) date_count
from date1
where col_date BETWEEN TO_DATE ('2009/01','yyyy/mm') and TO_DATE ('2009/12','yyyy/mm')
group by to_char (col_date,'yyyy/mm')
order by 1;

output:
COL_DAT DATE_COUNT
------- ----------
2009/01 1
2009/02 1
2009/03 1
2009/04 1
2009/05 1
2009/06 1
2009/07 1
2009/08 1
2009/09 1
2009/10 1
2009/11 1
2009/12 3
******* ----------
sum 14


Notice there are 4 entries not accounted for here
================================================================

ops$tkyte%ORA11GR1> select to_date('2009/01','yyyy/mm'), to_date('2009/12','yyyy/mm') from dual;

TO_DATE('2009/01','Y TO_DATE('2009/12','Y
-------------------- --------------------
01-jan-2009 00:00:00 01-dec-2009 00:00:00

where date_col >= TO_DATE ('2009/01','yyyy/mm') 
  and date_col < add_months( TO_DATE('2009/12','yyyy/mm'), 1 )

where date_col between TO_DATE ('2009/01','yyyy/mm')
                   and add_months( TO_DATE('2009/12','yyyy/mm'), 1 )-1/24/60/60;

Hi Tom

I am still having issues with dates

First
I am trying to figure out why the second date example is faster than the first when it looks like the first date example explain plan seems to be better
I am not very good at reading explain plans, so i am going by the times

When I run each example, the second example executes twice as fast as the first  (7 minutes compared to 18 minutes)

Second
The amount of data retrieved in example two for 2005 is far greater than the amount of data retrieved in example one so I am not understanding just why

Note: The data did not change while I was running the selects

This is the value for :v_startdate

12:23:44 SQL> select :v_startdate from dual;

:V_STARTDATE
--------------------------------
2005


Any help is greatly appreciated

The only thing I am changing in the sql statemenst is the where clause for a.auditdate 

Thanks
Jim
=============================================================
select  
   c.name group_name,
   TO_CHAR (a.auditdate,'yyyy') yeartodate,
   COUNT (a.auditdate) livelinklogins,
   COUNT (DISTINCT a.performerid) distinctusers
from  
   dauditnew a,
   kuaf b,
   kuaf c
where  
   a.auditstr = 'Login'
and 
   a.auditdate >= TO_DATE (:v_startdate,'yyyy')
and
   a.performerid = b.id
and
   b.groupid = :v_groupid
and
   b.groupid = c.id
group by 
   c.name,
   TO_CHAR (a.auditdate,'yyyy')
order by
   c.name,
   TO_CHAR (a.auditdate,'yyyy');


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
Plan hash value: 3974924054

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          | 27193 |  1513K|       |  1937   (1)| 00:00:24 |
|   1 |  SORT GROUP BY                 |                          | 27193 |  1513K|  1824K|  1937   (1)| 00:00:24 |
|*  2 |   HASH JOIN                    |                          | 27193 |  1513K|       |  1557   (1)| 00:00:19 |
|   3 |    NESTED LOOPS                |                          |   126 |  4284 |       |     6   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| KUAF                     |     1 |    24 |       |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | KUAF_PRIMARY             |     1 |       |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| KUAF                     |   126 |  1260 |       |     5   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | KUAF_GROUPID             |   126 |       |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID | DAUDITNEW                |   289K|  6509K|       |  1548   (1)| 00:00:19 |
|*  9 |     INDEX RANGE SCAN           | DAUDITNEW_INDEXDATEEVENT | 52163 |       |       |   419   (1)| 00:00:06 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."PERFORMERID"="B"."ID")
   5 - access("C"."ID"=TO_NUMBER(:V_GROUPID))
   7 - access("B"."GROUPID"=TO_NUMBER(:V_GROUPID))
   9 - access("A"."AUDITDATE">=TO_DATE(:V_STARTDATE,'yyyy') AND "A"."AUDITSTR"='Login')
       filter("A"."AUDITSTR"='Login')

flush shared pool
initialize variables
execute sql
log out

11:47:00 SQL> select
11:47:00   2     c.name group_name,
11:47:00   3     TO_CHAR (a.auditdate,'yyyy') yeartodate,
11:47:00   4     COUNT (a.auditdate) livelinklogins,
11:47:00   5     COUNT (DISTINCT a.performerid) distinctusers
11:47:00   6  from
11:47:00   7     dauditnew a,
11:47:00   8     kuaf b,
11:47:00   9     kuaf c
11:47:00  10  where
11:47:00  11     a.auditstr = 'Login'
11:47:00  12  and
11:47:00  13     a.auditdate >= TO_DATE (:v_startdate,'yyyy')
11:47:00  14  and
11:47:00  15     a.performerid = b.id
11:47:00  16  and
11:47:00  17     b.groupid = :v_groupid
11:47:00  18  and
11:47:00  19     b.groupid = c.id
11:47:00  20  group by
11:47:00  21     c.name,
11:47:00  22     TO_CHAR (a.auditdate,'yyyy')
11:47:00  23  order by
11:47:00  24     c.name,
11:47:00  25     TO_CHAR (a.auditdate,'yyyy');


Group        Year               LiveLink Connects Distinct Logins
------------ ---------------- ------------------- ---------------
Admin_GPSW   2005                           9,676             322
             2006                         142,974             518
             2007                         150,081             592
             2008                         166,149             848
             2009                         131,410             926
             2010                         120,271             944
************                  -------------------
sum                                       720,561

6 rows selected.

12:05:51 SQL>




=============================================================

select  
   c.name group_name,
   TO_CHAR (a.auditdate,'yyyy') yeartodate,
   COUNT (a.auditdate) livelinklogins,
   COUNT (DISTINCT a.performerid) distinctusers
from  
   dauditnew a,
   kuaf b,
   kuaf c
where  
   a.auditstr = 'Login'
and 
   TO_CHAR (a.auditdate,'yyyy') >= :v_startdate
and
   a.performerid = b.id
and
   b.groupid = :v_groupid
and
   b.groupid = c.id
group by 
   c.name,
   TO_CHAR (a.auditdate,'yyyy')
order by
   c.name,
   TO_CHAR (a.auditdate,'yyyy');



PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
Plan hash value: 3362169395

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     | 27193 |  1513K|       |   145K  (1)| 00:29:03 |
|   1 |  SORT GROUP BY                  |                     | 27193 |  1513K|  1824K|   145K  (1)| 00:29:03 |
|*  2 |   TABLE ACCESS BY INDEX ROWID   | DAUDITNEW           |   216 |  4968 |       |  1149   (0)| 00:00:14 |
|   3 |    NESTED LOOPS                 |                     | 27193 |  1513K|       |   144K  (1)| 00:28:59 |
|   4 |     NESTED LOOPS                |                     |   126 |  4284 |       |     6   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| KUAF                |     1 |    24 |       |     1   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | KUAF_PRIMARY        |     1 |       |       |     1   (0)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| KUAF                |   126 |  1260 |       |     5   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | KUAF_GROUPID        |   126 |       |       |     1   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN            | DAUDITNEW_INDEXUSER | 86701 |       |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("A"."AUDITSTR"='Login' AND TO_CHAR(INTERNAL_FUNCTION("A"."AUDITDATE"),'yyyy')>=:V_STARTDATE)
   6 - access("C"."ID"=TO_NUMBER(:V_GROUPID))
   8 - access("B"."GROUPID"=TO_NUMBER(:V_GROUPID))
   9 - access("A"."PERFORMERID"="B"."ID")

log in
flush shared pool
initialize variables
execute sql

12:09:30 SQL> select
12:09:30   2     c.name group_name,
12:09:30   3     TO_CHAR (a.auditdate,'yyyy') yeartodate,
12:09:30   4     COUNT (a.auditdate) livelinklogins,
12:09:30   5     COUNT (DISTINCT a.performerid) distinctusers
12:09:30   6  from
12:09:30   7     dauditnew a,
12:09:30   8     kuaf b,
12:09:30   9     kuaf c
12:09:30  10  where
12:09:30  11     a.auditstr = 'Login'
12:09:30  12  and
12:09:30  13     TO_CHAR (a.auditdate,'yyyy') >= :v_startdate
12:09:30  14  and
12:09:30  15     a.performerid = b.id
12:09:30  16  and
12:09:30  17     b.groupid = :v_groupid
12:09:30  18  and
12:09:30  19     b.groupid = c.id
12:09:30  20  group by
12:09:30  21     c.name,
12:09:30  22     TO_CHAR (a.auditdate,'yyyy')
12:09:30  23  order by
12:09:30  24     c.name,
12:09:30  25     TO_CHAR (a.auditdate,'yyyy');

Group        Year               LiveLink Connects DISTINCTUSERS
------------ ---------------- ------------------- -------------
Admin_GPSW   2005                          56,374           395
             2006                         142,974           518
             2007                         150,081           592
             2008                         166,149           848
             2009                         131,410           926
             2010                         120,271           944
************                  -------------------
sum                                       767,259

6 rows selected.

12:16:29 SQL>

select client_id, term_eff_from_date, term_eff_to_date
from customers
where client_id in (1, 2, 3)

I need to create SQL Statement based effective from date and effective to date per client. I have active client and I need to calculate number of years with my company

1 Client ID - John Smith

08/22/2005 - 08/22/2011
06/30/2005 - 07/14/2005
04/15/2005 - 04/15/2006

I need to find out number of years client with our company.

Results should be 2320 / 365 = 6.356164 years

2 ClientID: Jenny

03/01/1993 - 06/30/1999
06/30/1999 - 07/30/2001
09/01/2003 - 09/01/2007
11/20/2007 - 06/30/2011

I need to find out number of years client with our company.

Results should be: 5852 / 365 = 16.03288 years

Client 3:

Example
6/1/1982 - 03/20/1996
4/1/1972 - 02/20/1996

Then need convert
06/01/1982 - 03/20/1996
04/01/1972 - 06/01/1982 -> see date cancellation date

Results 8754 / 365 days = 23.98356

Thanks

Hi Tom

Would really appreciate if you could give some suggestions please.

I need to convert string to DATE in oracle.
I am getting values in the following formats all under one column-
5/31/2009 06:48 AM
6/1/2009 06:48:15 PM
etc.

So I cannot change the TO_DATE query as the column size is exceeding million records.

SELECT to_date('5/31/2009 06:48:09 AM','MM/DD/YYYY HH:MI:SS AM') FROM dual;

above query runs correctly, whereas lower one throws error.

SELECT to_date('5/31/2009 06:48 AM','MM/DD/YYYY HH:MI:SS AM') FROM dual;
-- it happens when there are 0 seconds so it just stores HH:MI and not HH:MI:SS

how can I convert using single TO_DATE command for all the columns?

your suggestions much appreciated.

Thanks,
Regards,
Hitin

Hello Hitin,

It appears that Tom is busy elsewhere, so I dare propose this answer although you may not see it:

WITH DATA AS (
  SELECT '5/31/2009 06:48 AM' INDATE FROM DUAL
  UNION ALL
  SELECT '6/1/2009 06:48:15 PM' INDATE FROM DUAL
)
SELECT TO_DATE(
  INDATE,
  CASE WHEN INDATE LIKE '%:%:%' THEN 'MM/DD/YYYY HH:MI:SS AM'
  ELSE 'MM/DD/YYYY HH:MI AM' END
) outdate from data;

OUTDATE
-------------------
2009-05-31 06:48:00
2009-06-01 18:48:15

Hi Tom,

I need to report the difference between two dates by excluding weekend dates ( i.e. Saturday & Sunday ). Consider the following:

create table date_tbl
(date1 date,
date2 date);

insert into date_tbl values(to_date('16-JUN-2011 13:00','DD-MON-RRRR HH24:MI'),sysdate);
insert into date_tbl values(to_date('17-JUN-2011 17:10','DD-MON-RRRR HH24:MI'),sysdate);
insert into date_tbl values(to_date('19-JUN-2011 10:13','DD-MON-RRRR HH24:MI'),sysdate);
insert into date_tbl values(to_date('18-JUN-2011 14:30','DD-MON-RRRR HH24:MI'),sysdate);
COMMIT;
select to_char(date1,'DD-MON-RRRR HH24:MI') date1,
to_char(date2,'DD-MON-RRRR HH24:MI') date2
from date_tbl;

So for the above,
(1) when calculating the difference between 16th & 20th of June 2011, I need to exclude 18th & 19th of June 2011.
(2) when calculating the difference between 17th & 20th of June 2011, I need to exclude 18th & 19th of June 2011.
(3) when calculating the difference between 18th & 20th of June 2011, I need to exclude 18th & 19th of June 2011.
(4) when calculating the difference between 19th & 20th of June 2011, I need to exclude 19th of June 2011.

Can a query be written to achive this. Please advise.

Many Thanks

Your suggestion was very helpful....

Hi Tom,
I need to get daily records between sysdate 00:00:00 to current sysdate like wise everyday i need to get these records so can you suggest me proper query for this

Thanks in advance..

i tried following
select * from tablename where TestTime between to_date(to_char(trunc(sysdate)||' 00:00:00'),'DD/MM/RRRR HH24:MI:SS') and to_date(to_char(trunc(sysdate)||' 23:59:59'),'DD/MM/RRRR HH24:MI:SS')

select * 
  from t
 where dt >= trunc(sysdate,'dd')
   and dt < trunc(sysdate+1,'dd')

Tom,

Following up on what you said "using math or trunc" how can I turn the following using math or trunc.

AND (((X.STUDENT_TS > TO_TIMESTAMP('2012/09/13 15:17:03.000000','YYYY/MM/DD HH24:MI:SS.FF'))
AND (X.STUDENT_TS <= TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE) +17/23,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS.FF')))
OR
((P.STUDENT_TS > TO_TIMESTAMP('2012/09/13 17:05:12.376629','YYYY/MM/DD HH24:MI:SS.FF'))
AND (P.STUDENT_TS <= TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE) +17/23,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS.FF'))))

Tom,

Thanks for looking at my code. I thought I was doing something wrong or it could have been written a bit better since you said "try to avoid ever using to_char/to_date to perform date stuff, use trunc, add_montths and math addition) to do that". I noticed that the Date range takes a lot longer than the
actual select/outer joined.


SELECT
A.STUDENT_ID,
A.BUDGET,
A.UNIVERSITY,
B.sUBSTEACHER,
A.PRINCIPAL,
A.DISTRICT,
B.SBR_ID,
B.univ_SEQ_NO
FROM
student A FULL OUTER JOIN student_region B
ON A.student_ID=B.student_id
AND A.region_NO=B.region_NO
AND A.univ_SEQ_NO=B.univ_SEQ_NO

A.REQUEST_STATUS_CODE IN ('LA', 'NY', 'NJ', 'DE', 'FL)
AND (((X.STUDENT_TS > TO_TIMESTAMP('2012/09/13 15:17:03.000000','YYYY/MM/DD HH24:MI:SS.FF'))
AND (X.STUDENT_TS <= TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE) +17/23,'MM/DD/YYYY
HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS.FF')))
OR
((P.STUDENT_TS > TO_TIMESTAMP('2012/09/13 17:05:12.376629','YYYY/MM/DD HH24:MI:SS.FF'))
AND (P.STUDENT_TS <= TO_TIMESTAMP(TO_CHAR(TRUNC(SYSDATE) +17/23,'MM/DD/YYYY
HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS.FF'))))
AND EXISTS (SELECT 1 FROM SCHOOL_DISTRICT
WHERE SCHOOL_DISTRICT.STUD_ID=A.STUD_ID)

Dear Tom,

Why is the output SYSDATE does not change per record if I issue below SQL? 

All along I thought that SYSDATE is like a function call so would behave much like if you issue a sql like 'SELECT DBMS_RANDOM.RANDOM FROM ALL_TABLES'.

SQL>SELECT SYSDATE from ALL_TABLES;
:
:
SYSDATE
--------------------------
02-nov-2012 09:54:50
02-nov-2012 09:54:50
02-nov-2012 09:54:50
02-nov-2012 09:54:50
02-nov-2012 09:54:50
02-nov-2012 09:54:50
02-nov-2012 09:54:50
02-nov-2012 09:54:50

3588 rows selected.

@Al Ricafort,

what does

SELECT DBMS_RANDOM.RANDOM FROM ALL_TABLES

return for you ?

I assume, a series of different pseudo-random numbers ?
( So it does in my Oracle Version at the moment. )

Please note, that the implementation of your SQL-engine is free to behave differently: it could return the same first chosen pseudo-random numbers all again ( it is free to behave for example in a "statement level deterministic" manner here also ).

See
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3181424400346795479
for an interesting discussion on a similar topic

@Sokrates

Thanks for the link. Very interesting discussion indeed.

Hi Tom,

I faced an issue with date format. I was trying to do
date formatting as below as I need to fetch data from a table in which data is stored against each week of day.
1 , 2 etc where 1 is MON , 2 is TUE etc.

In SQL developer when I run this query it returns me 7.
SELECT TO_CHAR(to_date('27/01/2013','dd/mm/rrrr'), 'D') FROM dual

So as an output of my program I was expecting the value against 7. When I executed the procedure from SQL Developer it gives the result correctly. but when the procedure is called form .NET it returned me data against day '1'. I am not sure whether I am missing anything in the logic, or whether someone else also faced this issue.

Now I changed my code like this
CASE
WHEN TO_CHAR(DATE_TAB.LOOP_DATE, 'D') =1
THEN 7
ELSE TO_CHAR(DATE_TAB.LOOP_DATE, 'D') -1
END and everything working OK.

I am confused. Whether this is a bug or am I doing something wrong.

Thanks and Regards
Sangeetha

ops$tkyte%ORA11GR2> SELECT TO_CHAR(to_date('27/01/2013','dd/mm/rrrr'), 'D' ) FROM dual;

T
-
1

ops$tkyte%ORA11GR2> alter session set nls_territory = germany;

Session altered.

ops$tkyte%ORA11GR2> SELECT TO_CHAR(to_date('27/01/2013','dd/mm/rrrr'), 'D' ) FROM dual;

T
-
7

ops$tkyte%ORA11GR2> alter session set nls_territory = america;

Session altered.

ops$tkyte%ORA11GR2> select dt, to_char(dt,'dy'), trunc(dt)-trunc(dt, 'IW')+1
  2    from ( SELECT to_date('27/01/2013','dd/mm/rrrr')+level-1 dt from dual connect by level <= 7);

DT        TO_ TRUNC(DT)-TRUNC(DT,'IW')+1
--------- --- --------------------------
27-JAN-13 sun                          7
28-JAN-13 mon                          1
29-JAN-13 tue                          2
30-JAN-13 wed                          3
31-JAN-13 thu                          4
01-FEB-13 fri                          5
02-FEB-13 sat                          6

7 rows selected.

ops$tkyte%ORA11GR2> alter session set nls_territory = germany;

Session altered.

ops$tkyte%ORA11GR2> select dt, to_char(dt,'dy'), trunc(dt)-trunc(dt, 'IW')+1
  2    from ( SELECT to_date('27/01/2013','dd/mm/rrrr')+level-1 dt from dual connect by level <= 7);

DT       TO_ TRUNC(DT)-TRUNC(DT,'IW')+1
-------- --- --------------------------
27.01.13 sun                          7
28.01.13 mon                          1
29.01.13 tue                          2
30.01.13 wed                          3
31.01.13 thu                          4
01.02.13 fri                          5
02.02.13 sat                          6

7 rows selected.

Oh OK. Thank you so much. In .NET when we executed weekday() function it was returning 0 for Sunday.
Anyways will try with this .

Thanks again
Have a great day ahead..

Regards
Sangeetha

Thank you so much for the inputs Tom.

Regards
Sangeetha

I have a partitioned table "x", partitioned by x_id and a record in it as follows:

my_dt is defined as a date.

select my_dt dt, to_char(my_dt, 'DD-MON-YYYY HH24:MI:SS' ) dt_fully_formatted from x where x_id = 'C_8504'

Output is as follows:

dt = 31/03/2013
dt_fully_formatted = 31-MAR-2013 00:00:00

I ran he folowing piece of code to set the nls_date_format:

alter session set nls_date_format = 'DD-MON-YYYY'

Now, if I do the following:

select my_dt dt, to_char(my_dt, 'DD-MON-YYYY HH24:MI:SS' ) dt_fully_formatted from x where x_id = 'C_8504' and my_dt = '31-MAR-2013'

I get zero records returned. Any ideas why?

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    my_dt  date,
  4    x_id   varchar2(6),
  5    y      varchar2(30)
  6  )
  7  partition by hash(x_id)partitions 8
  8  /

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t (my_dt,x_id,y) values ( to_date( '31-mar-2013' ), 'C_8504', 'hello world' );

1 row created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @bigdate
ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA11GR2> select dump(my_dt,16), my_dt from t where x_id = 'C_8504';

DUMP(MY_DT,16)
-------------------------------------------------------------------------------
MY_DT
--------------------
Typ=12 Len=7: 78,71,3,1f,1,1,1
31-mar-2013 00:00:00



ops$tkyte%ORA11GR2> create table tmp as select to_date( '31-mar-2013', 'dd-mon-yyyy' ) d from dual;

Table created.

ops$tkyte%ORA11GR2> select dump(d,16) from tmp;

DUMP(D,16)
-------------------------------------------------------------------------------
Typ=12 Len=7: 78,71,3,1f,1,1,1

Hi Tom,

I do not know, where this is a right place to ask this question. But it is quite urgent so I am asking this question, as you have blocked new question link.

I want to build WORM DB ( Write Once and Read Many ) in 12c. Nobody even DBA should be able to delete any DB transaction. This is my legal requirements. Is it possible to do so ? If yes can you elaborate in details ?

thanks and regards
PJP

I was poking around my DB and trying to find a performance difference between these 2 date formats when querying large datasets. All my partitions and indexs are on timestamp format.

is there any reason not to use option 1 or over option 2?

1) ORDER_DATE BETWEEN '2012-05-03 00:00:00' AND '2012-05-04 00:00:00'

2) ORDER_DATE BETWEEN to_date('20120503','yyyymmdd') and to_date('20120504','yyyymmdd')

thanks!

scott%ORA11GR2> create table t
  2  as
  3  select to_date( '3-may-2012 12:00:00', 'dd-mon-yyyy hh24:mi:ss' ) dt
  4    from dual;

Table created.

scott%ORA11GR2>
scott%ORA11GR2> alter session set nls_date_format = '...... hh24:mi:ss';

Session altered.

scott%ORA11GR2>
scott%ORA11GR2> select *
  2    from t
  3   where dt BETWEEN '2012-05-03 00:00:00' AND '2012-05-04 00:00:00'
  4  /

DT
-------------------
2012-05-03 12:00:00

scott%ORA11GR2>
scott%ORA11GR2> alter session set nls_date_format = '..... hh24:mi:ss';

Session altered.

scott%ORA11GR2> select *
  2    from t
  3   where dt BETWEEN '2012-05-03 00:00:00' AND '2012-05-04 00:00:00'
  4  /

no rows selected

fnd_request.submit_request 
                      (application => 'FND'
                       ,program     => 'FNDLGPRG'
                       ,start_time  => to_char(sysdate,'DD-MON-YYYY') || ' 20:00:00'
         ,argument1   => to_char(sysdate-4,'YYYY-MON-DD HH24:MI:SS')
         ,argument2 => chr(0)
                       );

I'm trying to schedule a concurrent request from backend directly to automate the process but start_time and argumen1 above are taking different date format because of that only its not getting scheduled i think. Its get executed only for the first time after it is scheduled but for subsequent runs argument1 value is taken as null, not sure why . Can you please help me in this case.

I got a question that i need help with can someone tell be the answer for it please

Apparently the date or time data types come with a default string. Why do you think is so

ops$tkyte%ORA11GR2> select sysdate from dual;

SYSDATE
---------
09-DEC-14

ops$tkyte%ORA11GR2> @bigdate
ops$tkyte%ORA11GR2> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

ops$tkyte%ORA11GR2> select sysdate from dual;

SYSDATE
--------------------
09-dec-2014 11:25:40

ops$tkyte%ORA11GR2> select to_char(sysdate, 'fmDay Month YYYY' ) from dual;

TO_CHAR(SYSDATE,'FMDAYMO
------------------------
Tuesday December 2014

but I need to catch the 30 days of every month.
all months days are 30. now, this will be possible in oracle and kindly give me function for find days 360.

69

So I use between to_date('2020/06/01','yyyy/mm/dd hh24:mi:ss' ) and To_date('2020/06/05','yyyy/mm/dd hh24:mi:ss') as a qualifier. However, the results don't reflect the time zone. In the select, I account for the time zone using this: To_char(cast((From_TZ(cast(p.BIRTH_DT_TM as TIMESTAMP),'+00:00') at time zone 'US/Mountain') as Date), 'YYYY-MM-DD') as DateOfBirth. Any thoughts?