Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Munzer.

Asked: February 23, 2002 - 1:30 pm UTC

Last updated: August 10, 2020 - 2:12 am UTC

Version: 8.1.7

Viewed 100K+ times! This question is

You Asked

Tom:

1. I had a problem with a date query using the operator 'between'.

WHen I do


Select * from table where date is between '01-JAN-02' and '17-JAN-02'

it does not give me the records marked with a date '17-JAN-02'
I have to change it to '18-JAN-02' to get those.

I thought the between operator use <= and >= so i should be getting anything on those dates and between those dates but it does not.

Any ideas.

2. As a developer, is there any way where I can check using the browser or SQL*PLUS whether a web application is set as "STATELESS" or "STATEFUL".

The dba said he changed it from stateless to stateful but nothing happened in speed. I want to confirm the configuration change?
Can you check all parameters set for the web application?

Thank you,



and Tom said...

1)
toms rule #1: never compare strings to dates and dates to strings. Always compare strings to string and dates to dates!

toms rule #2: stop using YY, just stop -- now, forever!!!. Always use to_date and ALWAYS use a 4 character year mask. Just do it! You will never be sorry you did. You WILL be sorry if you do not. (i still cannot believe we did not all learn this painful lesson in 2000!)

The problem you have is that:

where dt_col between '01-jan-02' and '17-jan-02'

is the same as:

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

So, the date: 17-jan-2002 12:00:00 (noon on jan 17th, 2002) is NOT between those two. You could:

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' )


or if you just wanted to supply the days as bind variables (in the form of a string like '01-jan-2002') you could:

where dt_col between to_date( :bv1, 'dd-mon-yyyy' )
and to_date( :bv2, 'dd-mon-yyyy' ) + (1-1/24/60/60)

that would do the same (adding 1-1/24/60/60 adds 23:59:59


2) if you are using the mod_plsql module, you should see database sessions that are connected persistently in "stateful" mode. (query v$session). changing from stateless to stateful wont necessarily affect performance unless you have lots of requests coming in simultaneously and actually connecting to the database is the bottleneck (on a smallish system, this will not be the case). if the PLSQL code or SQL code is what is slow, this setting won't make a bit of difference! Use tools like dbms_profiler and tkprof to check this. If you are interested -- my book has lots of details on using these tools to tune with.




Rating

  (102 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Date Query

Munzer, February 23, 2002 - 5:24 pm UTC

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 Kyte
February 23, 2002 - 6:20 pm UTC

1) you dont HAVE to use to_date -- I am strongly ENCOURAGING you to use to_date to avoid ambiguities (compare strings to strings, numbers to numbers, dates to dates -- don't RISK confusion comparing strings to numbers, dates to strings).

Always use YYYY formats as well!!!

Now as for: "SQL book says that between should find dates min>=date<=max." -- I tried to tell you -- IT IS DOING just that!!

It is because

17-jan-02 is really January 17th, 2002 at 0 hundred hours (eg: an hour before 1am on jan 17th).

Therefore, only dates in your database that are LESS THAN or EQUAL to January 17th, 2002 at 0 hundred hours would be returned. You wanted ALL dates between 0 hundred hours January 1st, 2002 and MIDNIGHT January 17th, 2002 -- hence you need to use a date that is MIDNIGHT January 17th, 2002 (well, really 23:59:59 on January 17th, 2002)....


with the p_fromdate and p_todate -- if the contents of those PLSQL variables is 01-jan-02 and 17-jan-02 (and I would BEG you to alter your interface to make it be 2002, not 02!) then you would code:

where date between to_date( p_fromdate, 'dd-mon-yy' )
and to_date( p_todate, 'dd-mon-yy' ) + (1-1/24/60/60)

that gets you from january 1st, 2002 at 0 hundred hours to midnight on january 17th, 2002.


2) yes, you would use owa_util.bind_variables if you are using owa_util.list_print/cells_print

3) Then on that app, the pages must have been very very fast and the connection was a large portion of the total request time (or for some reason the connection was taking much longer then it should).

If it is taking 5-6 seconds to run a page -- you have SLOW sql and/or SLOW PLSQL code. Using dbms_profiler and/or tkprof (start with tkprof) you will find out which it is. 5-6 seconds indicates your application itself is slow, not the connection (a connection should take a small fraction of a second).

As for packages -- another suggestion I have is that all REAL production code would use NOTHING other then packages and would never use a stand alone function. See </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:286816015990 <code>
for why. So yes, put EVERYTHING into packages. EVERYTHING.





date query

Munz, February 23, 2002 - 10:37 pm UTC

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 Kyte
February 24, 2002 - 9:28 am UTC

The above things are STRINGS, they are not dates. They are strings that can be converted into dates.


Maybe in your application, if someone enters

01-jan-30
01-jan-99

it is clear what that means. Depending on your default NLS_DATE_FORMAT that will either be:

01-jan-2030
01-jan-2099

OR

01-jan-2030
01-jan-1999

ambiguities like that scare the $#@ out of me. If you are going to allow them to enter

dd-mon-YY

then you better use a DATE format in your TO_DATE to avoid all ambiguities. I would suggest using

to_date( string, 'dd-mon-RR' )

and learn what the different between RR and YY is (see the SQL reference manual under date formats). In that scenario, no matter what database you install your code into - you KNOW whats going to happen. I don't like to rely on defaults, be explicit.

Best would be to have some javascript that as you TABBED out of the date field, it would REWRITE the date from dd-mon-yy into dd-mon-yyyy (your java script would look at the input, see if only two digits were supplied and supply the other two). In that fashion the end user need not supply the century but they will SEE the century that will be used -- giving them the opportunity to overwrite that with the correct century when needed.

v$session

Munz, February 27, 2002 - 2:04 pm UTC

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

Tom Kyte
February 27, 2002 - 3:50 pm UTC

none really. "stateful" and "stateless" is a WEB concept, done at the app server layer.

If you see lots of connected but "idle" sessions -- and the usernames correspond to the usernames of your web applications, then you probably have stateful.

select username, status from v$session where username is not null;





VL, February 27, 2002 - 9:20 pm UTC

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

Tom Kyte
February 28, 2002 - 10:05 am UTC

variation on a theme. same thing as the between does yes.

Stuart, February 28, 2002 - 12:30 pm UTC

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

Tom Kyte
February 28, 2002 - 1:22 pm UTC

Depends on how you think I guess. I'm trained to see:

1-1/24/60/60

and think that's 1 day minus 1 second. I see it so often -- that .99999 would confuse the heck out of me. I'm so used to adding an hour (1/24) or a minute (1/24/60) or so on -- that I always use that. Even when asked to add 4 hours 20 minutes, i'll code:

4*(1/24) + 20*(1/24/60)



Does this means all Dates in April ...?

Pascal, April 29, 2002 - 6:11 am UTC

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




Tom Kyte
April 29, 2002 - 7:44 am UTC

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)

 

Sorry , I forgot the ( Bracket there)

Pascal, April 29, 2002 - 8:00 am UTC

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;





Tom Kyte
April 29, 2002 - 8:46 am UTC

That is correct then.

Stateless vs Statefull

Johan Snyman, April 30, 2002 - 10:46 am UTC

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).



Tom Kyte
April 30, 2002 - 11:46 am UTC

You are correct. I never use (or even consider using) "stateful" connections myself.

Yes, v$session won't tell you if it is stateless or stateful.

Still wondering about some of this

Bill Bunke, May 21, 2002 - 11:06 am UTC

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.

Tom Kyte
May 21, 2002 - 12:15 pm UTC

That'll work -- that is true, with the TIMESTAMP type in 9i -- the 23:59:59 would miss 25:59:59.99999



Why so much trouble?

Kim Berg Hansen, May 22, 2002 - 7:54 am UTC

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

Tom Kyte
May 22, 2002 - 8:25 am UTC

Well -- on the "big brains" comment -- we are all just people. He saw my idea, saw the flaw (one second won't work in 9i with a timestamp type!) and corrected it. You just refined it -- that makes you a big brain too I guess!

That works as well -- very well and it is more terse. The other (with the between) seems to "say more" to me (when reading it) however the <> part does tend to confuse it for other readers.

Thanks for the followup.

Does Trunc will stop the index

Yamani, May 22, 2002 - 9:57 am UTC

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,


Tom Kyte
May 22, 2002 - 10:46 pm UTC

unless you have an index on TRUNC(dt_col) -- yes, using trunc on dt_col would obviate an index on dt_col.

followup to Bill's review

A reader, May 23, 2002 - 1:07 pm UTC

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')


Bill's followup to A Readers followup

Bill Bunke, May 28, 2002 - 1:24 pm UTC

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.

To_date PROBLEM

atul, August 16, 2002 - 1:23 am UTC

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

Tom Kyte
August 16, 2002 - 7:35 am UTC

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!!!

 

date insert

mo, January 09, 2003 - 10:30 am UTC

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,

Tom Kyte
January 09, 2003 - 1:30 pm UTC

why that would "create a problem in validating primary key" is way beyond my understanding.

Why would you add the rather RANDOM string of digits that happen to be the current hour, minute, second? Why not just use 00:00:00 (midnight)? It is as VALID AS ANY OTHER TIME OF THE DAY is it not????

I would use 00:00:00 and figure out why I did wrong in my model that I felt compelled to add a random number to my date to "fix a validation problem"



update updates data -- yes. just update the data???

another query

PK, January 09, 2003 - 3:36 pm UTC

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

Tom Kyte
January 09, 2003 - 6:19 pm UTC

You can:

create or replace function my_to_date( p_string in varchar2 ) return date
as
begin
return to_date( p_string, 'yyyy-mm-dd' );
exception
when others then return null;
end;


instead -- then

select my_to_date( id ) from temp1;



Wrong result set

PK, January 10, 2003 - 2:16 pm UTC

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.

Tom Kyte
January 10, 2003 - 2:30 pm UTC

null spaces are not null spaces. they are blanks.

put whatever logic you need into my_to_date to make it work for you.

like , =

umesh, February 21, 2003 - 7:47 am UTC

  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 

Tom Kyte
February 21, 2003 - 10:23 am UTC

what is your point? you get what you ask for.

your default date format is apparently dd-mon-yy or maybe rr.


to_date(hiredate,'dd-mon-yyyy:hh24-mi-ss')

is really:

to_date( TO_CHAR(hiredate,'dd-mon-yy'), 'dd-mon-yyyy:hh24-mi-ss' )



there are soooo many things wrong with these two queries -- where to begin....

a) first you are using to_date on a column that is ALREADY A DATE. That means hiredate is turned into a string using the default format (dd-mon-rr) and then converted back into a date -- and you used YYYY sooooo guess what year that hiredate is??? 1982 or 2082 or maybe even something more bizzare?? Watch:

scott@ORA920> select to_date(hiredate,'dd-mon-yyyy:hh24-mi-ss'),
2 to_char(to_date(hiredate,'dd-mon-yyyy:hh24-mi-ss'),'yyyy'),
3 to_char(hiredate,'yyyy')
4 from emp
5 /

TO_DATE(H TO_C TO_C
--------- ---- ----
17-DEC-80 0080 1980
20-FEB-81 0081 1981
22-FEB-81 0081 1981
02-APR-81 0081 1981
28-SEP-81 0081 1981
01-MAY-81 0081 1981
09-JUN-81 0081 1981
09-DEC-82 0082 1982
17-NOV-81 0081 1981
08-SEP-81 0081 1981
12-JAN-83 0083 1983
03-DEC-81 0081 1981
03-DEC-81 0081 1981
23-JAN-82 0082 1982

14 rows selected.


so,
rule number 1 -- beware of IMPLICIT CONVERSIONS OF DATES and NUMBERS!!!
rule number 2 -- never to_number a number, never to_date a date



b) in the second query you make the same mistake as the first but get unintentially "lucky". LIKE works on strings -- sooooo that predicate is "where string like string". Soooo there is yet another implicit conversion, there are to_char's wrapped about those to_dates:

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


so, yes, when you COMPARE the strings:

23-JAN-82 with 23-JAN-82

they are "like" eachother. However, if you looked at the REAL DATES -- you see that you just had a hit on

23-jan-0082 with 23-JAN-1982


eg: you got the wrong answer in general.

sooo

rule number 3, compare dates to dates, strings to strings, numbers to numbers and never anything other combination

rule number 4, always be on the lookout for implicit conversions. You have 6 of them in your two queries. They are the cause of all of your problems







date = date character like character

umesh, February 23, 2003 - 10:59 pm UTC

Thank you Tom. I learn something daily from you

problems with date?

Alejandro Berrones Cruz, March 07, 2003 - 4:15 pm UTC

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

Tom Kyte
March 07, 2003 - 6:20 pm UTC

no, you don't. Plain and simple, you do not have 10 records with that date.


select to_date( hiredate, 'mm/dd/yyyy hh24:mi:ss' ) from emp
where to_char(hiredate,'mm/dd/yyyy') = '05/03/2003';


report back what THAT says.

Date conversion.

A Reader, May 30, 2003 - 11:48 am UTC

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 Kyte
May 30, 2003 - 12:04 pm UTC

in 9i, there is an interval type -- so yes.

in 8i, diy:

</code> http://asktom.oracle.com/Misc/DateDiff.html <code>

Interval Arithmatic on systimestamp?

jochuan, August 07, 2003 - 1:43 pm UTC

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 Kyte
August 09, 2003 - 5:38 pm UTC

you would have to implement a user defined aggregate right now.

here is a simple sum

create or replace type day_to_second_sum_type as object
(
total interval day to second,

static function
ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type )
return number,

member function
ODCIAggregateIterate(self IN OUT day_to_second_sum_type ,
value IN interval day to second )
return number,

member function
ODCIAggregateTerminate(self IN day_to_second_sum_type,
returnValue OUT interval day to second,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT day_to_second_sum_type,
ctx2 IN day_to_second_sum_type)
return number
);
/
create or replace type body day_to_second_sum_type
is

static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type)
return number
is
begin
sctx := day_to_second_sum_type( numtodsinterval( 0, 'SECOND' ) );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type,
value IN interval day to second )
return number
is
begin
self.total := self.total + value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN day_to_second_sum_type,
returnValue OUT interval day to second,
flags IN number)
return number
is
begin
returnValue := self.total;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type,
ctx2 IN day_to_second_sum_type)
return number
is
begin
self.total := self.total + ctx2.total;
return ODCIConst.Success;
end;


end;
/

CREATE or replace
FUNCTION ds_sum(input interval day to second )
RETURN interval day to second
PARALLEL_ENABLE AGGREGATE USING day_to_second_sum_type;
/




reader, August 28, 2003 - 12:19 pm UTC

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!


Tom Kyte
August 29, 2003 - 7:48 am UTC

umm, and exactly why might the i_date be in the year "2"????

select i_date
from t
where i_date >= add_months(sysdate,12);

would be the most appropriate way to do that and if you actually have dates with the year "2" in them -- you need to *fix* that data. it is obviously wrong.

comparing time...

A reader, March 19, 2004 - 7:44 am UTC

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


Tom Kyte
March 19, 2004 - 9:54 am UTC

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. 

A reader, March 19, 2004 - 7:46 am UTC

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)

installation

haron, March 19, 2004 - 1:27 pm UTC

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 for the query

A reader, March 20, 2004 - 6:08 am UTC

Thanks a lot TOM....


ANY IDEAS ABOUT DOING THIS TOM

LOU, July 27, 2004 - 6:21 pm UTC

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

Tom Kyte
July 27, 2004 - 7:49 pm UTC

see the other place you asked the same exact thing.

Thanks Tom

Lou, July 28, 2004 - 9:36 am UTC

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 Kyte
July 28, 2004 - 1:05 pm UTC

ask the manager to add january 1st to january 15th, I'd be interested in the answer :)

Using between for date queries

Dave, November 10, 2004 - 11:25 am UTC

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.

Tom Kyte
November 10, 2004 - 12:25 pm UTC

totally insufficient information here.

I see really good performance, i used EMP though :)



Dave, November 10, 2004 - 12:34 pm UTC

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 Kyte
November 10, 2004 - 2:32 pm UTC

who said the subquery "runs first"

you have given me virtually nothing to work with here.



Few more info about the above sql

A reader, November 10, 2004 - 12:51 pm UTC

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>  

Tom Kyte
November 10, 2004 - 2:33 pm UTC

how do you gather statistics. *exact*, *precise* command

and, are they up to do.

bitmap access plan ...

Gabe, November 10, 2004 - 3:54 pm UTC

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.

date range query

mo, December 09, 2004 - 7:01 pm UTC

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 Kyte
December 10, 2004 - 1:00 am UTC

too bad the designers of this system made simple things soooooo hard.

a date is a date

a string is a string

a string isn't a date.

sigh.


they have convienently made this pretty much impossible to do efficiently - without a function based index (if you feel an index would be right)


create index date_idx on t( to_date( Invoice_month || ' ' || invoice_fy, 'Mon yyyy' ) );

where to_date( .... ) >= :bv1 and to_date( .... ) < add_months(:bv2,1);



2) not a chance --those two fields a DATE COLUMN -- period, they are not two fields AT ALL, they are a DATE!



date range query

mo, December 13, 2004 - 11:07 am UTC

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 Kyte
December 13, 2004 - 1:47 pm UTC

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

and that is what a date does. 01-dec-2004 -- month = dec, year = 2004.


do i see a problem with your last thing -- umm, yup, huge. Your original question was:

(Jan 2003 - March 2004).


Ok, so tell me -- in two fields -- how do you do that one nicely??? :)

this is a DATE

sure, you could use a single 6 digit number:

YYYYMM

but even that implementation is horribly flawed as it includes NO EDITING that the date is valid and it HIDES information from the optimizer.


You have a DATE.

mo, December 13, 2004 - 7:31 pm UTC

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.

Tom Kyte
December 13, 2004 - 7:37 pm UTC

i think you meant to say

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

but even if not, your answer is wrong (for your original query and the one I assume you mean)

and you get......

jan 2003
feb 2003
mar 2003
jan 2004
feb 2004
mar 2004


think about it...... what about july 2003? month = 7, 7 is NOT between 1 and 3.

You would have to say:

where (year = 2003)
or (year = 2004 and month between 1 and 3)


to paraphrase an old but popular commercial

"dude, you gotta get a DATE field"

http://asktom.oracle.com/~tkyte/Misc/DateDiff.html has a bug

Vladimir Andreev, December 14, 2004 - 8:02 am UTC

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 Kyte
December 14, 2004 - 9:07 am UTC

thanks!

date range query

mo, December 14, 2004 - 3:33 pm UTC

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,

Alex, May 13, 2005 - 9:08 am UTC

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. 

Tom Kyte
May 13, 2005 - 10:44 am UTC

and file_num = null

will never

a) be true
b) be false

it will always be

c) i have not any idea if it is true or false



where file_num is NULL


there are SO many things wrong in this tiny example!!!!!! so many!!!

a) and file_num = null

has to be and file_num is null


b) and id_num = '1'

has to be and id_num = :x and a NUMBER type must be bound in!!!, you do not ever compare a number to a string or a string to a number, compare numbers to numbers only.


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')





Alex, May 13, 2005 - 11:57 am UTC

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. 

Tom Kyte
May 13, 2005 - 12:39 pm UTC

you gave me a query, I commented on said query -- period.



and file_num = NULL
will never ever ever ever work right.

if pfile_num is null, where file_num = pfile_num will not return ANY rows.


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' )


and that is probably the cause of the error.


you take the date in v_comp_date and convert it to a string using the default date mask, probably dd-mon-yyyy

you take that dd-mon-yyyy string and try to convert it back into a date with mm/dd/yyyy

the formats do not MATCH. and error......


you have a date, do not "to_date" a date.


The implicit conversions going on in this code are *scary*. You need to look twice at this

numbers to numbers
dates to dates
strings to strings
beware the implicit conversion.


lose the to_date in the query and it'll run and say "Still go no rows" because of line 11.

and fix line 4







Alex, May 13, 2005 - 3:23 pm UTC

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

Tom Kyte
May 13, 2005 - 4:31 pm UTC

if comp_date is null, you return 01/01/1900.

but TO_DATE (NVL ('6/1/2005', '01/01/1900'), 'MM/DD/YYYY') always returns

6/1/2005


so..... given those inputs, they are the same as comp_date = to_date()

Last DML Date

RUMMAN, May 14, 2005 - 5:53 am UTC

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 Kyte
May 14, 2005 - 9:38 am UTC

just keey the history of when it was last updated and in the report, you can use lag() and lead() to get what you need.


Not following your example entirely, but this is something that should be retrieved during REPORTING, not during transacting.

Alex, May 16, 2005 - 2:15 pm UTC

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 Kyte
May 16, 2005 - 4:02 pm UTC

create table t ( x varchar2(5) primary key );

insert into t values ( '5' );


select * from t where x = 5;
select * from t where x = '5';

compare the differences.........

when an implicit conversion leads to implicitly applying a function to an indexed column, that index won't be used.

that first query is really

where to_number(x) = 5;

no index on x will be used.

A reader, May 18, 2005 - 12:36 pm UTC

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?

Tom Kyte
May 18, 2005 - 12:57 pm UTC

we get time from the Operating system when we need to get the time.

check the OS clock and the time we report goes with it.

Why implicit date conversion fails

Rajeswari, November 23, 2005 - 4:35 am UTC

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
 

Tom Kyte
November 23, 2005 - 9:48 am UTC

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) 

Implicit date conversion

Rajeswari, November 24, 2005 - 5:18 am UTC

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;
/




Tom Kyte
November 24, 2005 - 8:52 am UTC

seriously doubt a jdbc issue, more likely - the NLS_LANG is being set in the java client environment and therefore:

</code> http://asktom.oracle.com/Misc/NLSDateFormat.html <code>


but really - it seems since you are in test, you can fix the oversight made by the developers in the last release and have better resulting code as a result!

any trace to get implicit date conversion format

Rajeswari, November 25, 2005 - 12:54 am UTC

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.


Tom Kyte
November 25, 2005 - 10:07 am UTC

you'd have to query it out, it is not in a trace.

Thanks

Rajeswari, November 29, 2005 - 12:13 am UTC

Thanks a lot Tom.

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

Query by date

cequal, December 19, 2005 - 11:17 am UTC

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


help on a query

vijay, January 19, 2006 - 7:53 am UTC

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


Tom Kyte
January 19, 2006 - 1:23 pm UTC

select customer
from t
where prd like 'PRD-3%' or prd like 'PRD-6%'
having count(distinct substr(prd,1,5) ) = 2

is *likely* it, but you give insufficient data to fully answer. can there be a prd-55-132 and so on. I'm guessing.


TO_DATE(date column)

Partha, January 20, 2006 - 2:52 am UTC

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 ?

Tom Kyte
January 20, 2006 - 10:21 am UTC

it was NOT displaying the wrong dates.

You had a bug in your developed application that caused you to format data improperly :)


to_date( start_date, 'yyyy/mm/dd' ) is the same as:

to_date( TO_CHAR(start_date), 'yyyy/mm/dd' )

so, what is your DEFAULT DATE FORMAT, you turned a date into a string, then into a date again.


as for the last question - look at your formats. you have dd/mm/yyyy and then you say "please pretend that is rrrr/mm/dd" - how the could that work?

thanks for the answer but my problem stil remains

Vijay, January 24, 2006 - 2:30 am UTC

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

Tom Kyte
January 24, 2006 - 8:09 am UTC

where column like 'PRD-3-%' or column like 'PRD-6-%'

is what you are looking for then

Date Questiton

Yoav, February 02, 2006 - 7:34 am UTC

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.

Tom Kyte
February 02, 2006 - 12:05 pm UTC

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.
 

How do I do sum() on a date/time interval value?

Andrew Piskorski, February 10, 2006 - 8:42 am UTC

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
 

Tom Kyte
February 10, 2006 - 12:59 pm UTC

I cannot answer "why"

I can only say "it does not"

To Andrew

Michel Cadot, February 10, 2006 - 1:30 pm UTC

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 

Date Question

Yoav, February 19, 2006 - 8:57 am UTC

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.

Tom Kyte
February 19, 2006 - 9:12 am UTC

you are sorting by a string (again). you are sorting STRINGS not dates.

I sorted a date.

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




Why sysdate in dual shows different

Alay, February 21, 2006 - 12:22 am UTC

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?
 

Tom Kyte
February 21, 2006 - 7:38 am UTC

date is running on your client, that is your clients PC date.

sysdate is from the server.

What it does ?

Tanmoy Choudhury, May 25, 2006 - 9:21 am UTC

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

Tom Kyte
May 25, 2006 - 1:47 pm UTC

just an internal'ism. nothing you would use.

Why Does Explain Plan Change, switching 2 'RR' to 4 'YYYY'dates

JK Purtzer, September 12, 2006 - 2:08 pm UTC

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


Tom Kyte
September 12, 2006 - 5:40 pm UTC

MM/DD/YYYY is a constant

MM/DD/RR is variable

that is why, one is known at parse time definitely, the other one can change at runtime (true, only every 100 years, but it is NOT deterministic)

Question for you, what is this date:

TO_DATE('8/7/06', 'MM/DD/RR')

no matter what you answer, you are wrong, because I'll change "sysdate" and make it different.

the cost is a reflection of the estimated rows flowing out of the step of the plan, if the plans are the "same" steps - they will run at the same speed.

Naive Date Question

A reader, September 14, 2006 - 2:59 pm UTC

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 Kyte
September 14, 2006 - 4:38 pm UTC

if you have a rule that says "invoice date is a date without time" and you believe some applications might put the time in - you can either

a) use the trigger to silently modify what they inserted. I believe this to be a bad idea in practice - I hate it when triggers silently modify my carefully inserted data.

b) use a constraint "check invoice_date = trunc(invoice_date)" to enforce your business rule and have the errant applications do the right thing.

Changing Date from one country to the other

Phillip Sangha, February 15, 2007 - 1:53 pm UTC

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


Changing Date from one country to the other --any feedback

Phillip Sangha, February 16, 2007 - 12:22 pm UTC

Hi Tom,

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

Thanks
Phillip
Tom Kyte
February 17, 2007 - 11:12 am UTC

i would not use new_time, in 9i and above there is complete timezone support builtin to the timestamp datatype - you should read up on that type.

There is the concept of a database timezone, and a client timezone - that is more likely what you want.

Converting varchar2 to timestamp

A reader, February 24, 2007 - 10:17 am UTC

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
Tom Kyte
February 26, 2007 - 1:08 pm UTC

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

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

Why di I get Feb. 30, Feb 31 in a date column?

A reader, March 02, 2007 - 10:52 am UTC

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


Tom Kyte
March 04, 2007 - 6:17 pm UTC

and what exactly loaded these dates - I'll guess that a custom program did and they used the binary date format (which we just accept the 7 bytes of data from them without checking) and they messed up.

OK

Kumar, April 27, 2007 - 8:09 am UTC

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
Tom Kyte
April 27, 2007 - 11:08 am UTC

because time is forever marching on and we are powerless to stop it.

the dates have a time component (hour, minute, second...)


by the time you query using sysdate, the clock has advanced and the created time is not equal to right now.


OK

A reader, May 03, 2007 - 2:47 am UTC

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?

Tom Kyte
May 03, 2007 - 5:36 pm UTC

You would have to know the time of object creation

and apparently, you do not.

so, you do not have the inputs for this query.

Tell me, why do you need to do this?

OK

A reader, May 04, 2007 - 2:27 am UTC

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.
Tom Kyte
May 04, 2007 - 12:59 pm UTC

sure, you can do that. not by using SYSDATE since that is RIGHT NOW.

but you didn't use sysdate-2, sysdate-2 would give you everything created in the last 48 hours.

Checking the date format

Roy, May 04, 2007 - 11:23 am UTC

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.
Tom Kyte
May 04, 2007 - 1:23 pm UTC

MM accepts the string MAY

04 when applied to YYYY is the year "4"
07 when applied to DD is the 7th day

it is all 'good'

A reader, May 30, 2007 - 8:55 am UTC

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.
Tom Kyte
May 30, 2007 - 3:43 pm UTC

if you just have "time", the interval datatype pops immediately into mind.

why

tom, July 12, 2007 - 4:50 pm UTC

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


Tom Kyte
July 12, 2007 - 5:12 pm UTC

you are comparing two strings there - yes. that is "obvious"

if we run this on the last day of this year, you'll get " Not 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


ORA 1858

Anto, February 20, 2008 - 6:35 pm UTC

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
Tom Kyte
February 20, 2008 - 8:49 pm UTC

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

v_comp_date was ALREADY A DATE

when they did:

to_date( v_comp_date, 'mm/dd/yyyy')

that was REALLY

to_date( to_char(v_comp_date).....

there was the implicit to_char.

so, one wonders... did they really know what they were doing (my gut reaction is "probably not")



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

yes, the DEFAULT DATE MASK FOR THE DATABASE/SESSION.

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


chew on that for a while and decide if implicit conversions and to_date/to_char calls WITHOUT a format are a good thing...

ORA 1858

Anto, February 21, 2008 - 10:17 am UTC

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
Tom Kyte
February 21, 2008 - 11:17 am UTC

what I would say is:

a) avoid always ALL IMPLICIT conversions, if you need to turn a string into a number, a number into a string, a date into whatever - be explicit

b) and when you are being explicit using to_date or to_char - USE A FORMAT - never rely on what you think the default format would be

query involving date columns

Harschil Kaparwan, October 21, 2008 - 8:25 am UTC

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

Tom Kyte
October 21, 2008 - 3:53 pm UTC

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


I don't need an example to explain "how" setting those events will
a) disable redundant range predicate removal
b) disable removal of constant predicates


It is after all - what they do, it is their "definition". You are hitting an optimizer related issue, setting those events disables some features of the optimizer - avoiding the issue you are encountering. That is all.

query involving date columns ...

Manoj, October 21, 2008 - 4:45 pm UTC

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

Tom Kyte
October 22, 2008 - 8:18 am UTC

...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....


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.

query involving date columns ...

Manoj, October 22, 2008 - 11:55 am UTC

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.



Tom Kyte
October 22, 2008 - 4:41 pm UTC

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

probably the same amount of regression testing you would do with a patch.

... 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.
...

I'm not worried about the query you were trying to fix, I'm worried about every other query in the system - this can and will change plans.

When plans change, one of three things happen.....

things go better
things go the same
you can guess this one....

implicit conversion direction

Michal Pravda, March 06, 2009 - 4:05 am UTC

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
Tom Kyte
March 06, 2009 - 10:29 am UTC

... I want to understand why Oracle chooses this "unsafe" direction. ...

in order to be predicable and deterministic.

In some countries, the number one and one half would be:

1.5

In others,

1,5


You want to get the same answer (or fail - failure is better than wrong answers) everywhere regardless of the NLS settings.


So, converting a number to a string implicitly would be HORRIBLY unsafe - you would get different answers against the same data!!!!!!

Also, if the data is to be considered a number, but is stored in a string, what would happen if the strings contained:


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

and you had a number with zero in it. You would be expecting all rows, you would get.... one row.


So, it is

a) NLS settings - would result in different answers
b) the fact that there are a LOT of ways to store the number zero in a string, but they are all "zero"



I am bringing my son with me to Praha for the week - really looking forward to it (wish it were warmer ;) )

implicit conversions

Michal Pravda, March 10, 2009 - 10:06 am UTC

Thank you,

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

Missing Something Using Between With Dates

Jim, December 03, 2009 - 1:20 pm UTC

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
================================================================


Tom Kyte
December 04, 2009 - 4:17 pm UTC

to date with yyyy/mm gives you the first DAY of the month.

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


if you want everything between jan-1st at 00:00:00 of 2009 through midnight 31-dec 2009 - you woud use either:
where date_col >= TO_DATE ('2009/01','yyyy/mm') 
  and date_col < add_months( TO_DATE('2009/12','yyyy/mm'), 1 )

(where it is larger than or equal to jan-1st 2009 at 00:00 and less than jan-1st 2010 at 00:00)

or

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


I prefer the former

Follow-Up

Jim, December 07, 2010 - 2:30 pm UTC

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>

SQL Scripts

John, June 10, 2011 - 3:05 pm UTC

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
Tom Kyte
June 10, 2011 - 3:27 pm UTC

no create
no inserts
no look


string to date convertion

hitin, June 16, 2011 - 7:27 am UTC

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
Tom Kyte
June 17, 2011 - 1:38 pm UTC

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


that doesn't make sense to me, why can't you? You can do almost anything you want.

1,000,000 is such a small number these days too.


Tell us what the possible formats for this data are - all of them. If you cannot, then you cannot convert this data (that should be obvious). But tell us what all of the input formats will look like, and then we can tell you how to do it.

@hitin from india: string to date conversion

Stew Ashton, June 16, 2011 - 12:52 pm UTC


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

Tom Kyte
June 17, 2011 - 1:40 pm UTC

as long as the only two formats are that - yes, I was going to propose a case statement with like's to find the correct format when I got the list myself :)

Date difference

Reader, June 20, 2011 - 9:53 am UTC

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


Tom Kyte
June 20, 2011 - 1:49 pm UTC

read some of these discussions:

http://asktom.oracle.com/pls/asktom/asktom.search?p_string=business+days

(read them from top to bottom - the answers evolve and get better over time :) )

Thank You Stew Ashton

hitin, June 21, 2011 - 5:22 am UTC

Your suggestion was very helpful....

Please Help In Following

Monty, October 05, 2011 - 8:00 am UTC

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')
Tom Kyte
October 05, 2011 - 10:57 am UTC

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


try to avoid ever using to_char/to_date to perform date stuff, use trunc, add_montths and math (addition) to do that.

How can I write this better using Math or Trunc?

Marco, October 29, 2012 - 2:11 pm UTC

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 Kyte
October 29, 2012 - 9:05 pm UTC

how about you tell us what you are trying to do.


but it looks like you've already done it correctly.



where database_column > ? and database_column <= ?

that is good. you are not using a function on the database column unnecessarily

Thanks for Looking at it.

marco, October 30, 2012 - 3:06 pm UTC

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)

select SYSDATE from any_table;

Al Ricafort, November 01, 2012 - 9:12 pm UTC

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.

Tom Kyte
November 01, 2012 - 10:31 pm UTC

sysdate is statement level deterministic by design, it would be *useless* otherwise in SQL (think about it)

select * from big_table where created between trunc(sysdate) and trunc(sysdate)+1-1/24/60;

if sysdate was changing - that query would return some really strange results wouldn't it. If you ran that query right before midnight, you'd get some records from today - and some from 'tomorrow' when the clock advances past midnight. You would never be able to use sysdate if it wasn't this way.

SELECT DBMS_RANDOM.RANDOM FROM ALL_TABLES

Sokrates, November 02, 2012 - 3:09 am UTC

@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

SELECT DBMS_RANDOM.RANDOM FROM ALL_TABLES

Al Ricafort, November 04, 2012 - 8:37 pm UTC

@Sokrates

Thanks for the link. Very interesting discussion indeed.

Sangeetha P, February 01, 2013 - 11:14 am UTC

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
Tom Kyte
February 01, 2013 - 3:19 pm UTC

you have different NLS settings when you run sqldeveloper than you did when you used .net.

specifically, the nls_territory must be different (it'll default from your NLS_LANG which is probably what is really different)

http://docs.oracle.com/cd/B12037_01/server.101/b10749/ch3globe.htm#1006576

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



different places in the world consider different days to be the first day of the week.


that said... we can use IW (ISO standard- worldwide) with trunc and a bit of math to do this:

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.

Sangeetha P, February 02, 2013 - 10:52 pm UTC

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
Tom Kyte
February 04, 2013 - 9:44 am UTC

and if you look at weekday()

http://tinyurl.com/d7l3d3f

you can see they default the first day of the week to sunday in all locations - but allow you to override it with an optional second parameter. we rely on NLS settings for an application to ascertain the correct default.

beware of first days of weeks - in all API's - they should all be location dependent (or they are wrong ;) )

Sangeetha P, February 18, 2013 - 11:42 pm UTC


Thank you so much for the inputs Tom.

Regards
Sangeetha

No Date being returned

Bob, September 06, 2013 - 11:14 am UTC

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?



Tom Kyte
September 10, 2013 - 9:24 am UTC

never ever rely on implicit conversions, not in C, not in Java, not in SQL, not in PLSQL. bad things always happen.

you don't really give me much to go on here, no creates, no inserts.

this is what I would do to start debugging this.

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



You'll want to verify that the "dump" output is identical (use dump against a database column - do not dump( to_date( ... ) ) - that won't work right.)


if they do not match, then you might have a corrupt date in your table - that can easily be introduced by an application trying to be too "cute" (they bind a native 7 byte date and we accept whatever they give us, I've seen it all too often in many C programs...)

DB WORM

Parag PATANKAR, September 16, 2013 - 6:46 am UTC

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
Tom Kyte
September 24, 2013 - 4:50 pm UTC

your choices currently include:

a) you use discrentionary access control (grant) to permit only INSERT and SELECT.
b) you enable auditing for all users, including sys

in the event of an audit event - you would know immediately that a change has been made and you have access to flashback database, flashback table, point in time recovery to recover it

or, using flashback data archive, you maintain a secure history of all data at the row level. in the event of an audit event - you would be able to see the history of changes and see the original data

there isn't an "insert only" construct out of the box.

When you use Date Formats

nd, October 11, 2013 - 7:15 pm UTC

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!
Tom Kyte
November 01, 2013 - 6:04 pm UTC

you would want to use a date literal:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51062

like "DATE '1998-12-25'"

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




you are relying on an NLS setting to be in place. if that assumption doesn't hold true - you lose.

NEVER rely on defaults.

always use a format with strings that contain dates.


or use a DATE LITERAL which has a fixed format that cannot be changed.

Date format conversions

Bala, March 10, 2014 - 9:08 am UTC

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.

DataBase

Sevim, December 09, 2014 - 5:42 am UTC

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
Tom Kyte
December 09, 2014 - 4:28 pm UTC

they do not.

dates are 7 bytes of binary information
timestamps are 7 to 11 bytes of binary information


when a program, such as sqlplus, selects them - they typically bind them to a string host variable. We will implicitly convert the bytes into a friendly string using an NLS setting - such as the NLS_DATE_FORMAT which defaults to DD-MON-RR:

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



but you can change that default as I showed


better yet is to EXPLICTLY convert:

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

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



That way you know what you are getting!


by the way, if you select date into a java date type for example, it'll be in java's internal way to represent a date - it isn't a 'string'. it is only when a program fetches the data into a host variable that is a string that this implicit conversion will happen!!!


dates are not strings are not dates

dates are not stored with a default string

a date is converted into a string using the NLS_DATE_FORMAT which can be specified in the to_char call, at the session level or defaulted in the databases init.ora

Days360

Harsh shah, November 12, 2019 - 7:27 am UTC

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.
Chris Saxon
November 12, 2019 - 11:53 am UTC

all months days are 30

Nope! February has 29 at most!

What exactly are you trying to do?

Post a new question with full details of the problem.

ii

57, April 21, 2020 - 4:23 pm UTC

69

How do I offset time zone

Adi, August 07, 2020 - 9:52 pm UTC

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?
Connor McDonald
August 10, 2020 - 2:12 am UTC

"It depends"

I mean - what assumptions are you making here? They were born in the same place as the server? etc etc...

Not entirely sure what you are asking here

I was 4 in 2000 fyi

A reader, February 08, 2021 - 4:21 pm UTC


More to Explore

DBMS_PROFILER

More on PL/SQL routine DBMS_PROFILER here