trunc to_date to_char

sunsail · #1 (**permalink**) 07-02-09, 07:26

Hello
can you explain how I should use these funcs?
I generally do

trunc(date)='01-01-2009'
to_date(date)='01-01-2009'
to_char(date)='01-01-2009'

in which situations should I use these

Thanks

flyboy · #2 (**permalink**) 07-02-09, 08:57

Quote:

Originally Posted by sunsail

trunc(date)='01-01-2009'
to_date(date)='01-01-2009'
to_char(date)='01-01-2009'

in which situations should I use these

For your own good, never use any of these constructions.

I do not know, which "situations" you face; anyway, the best ways for checking, whether <date> column of DATE data type belongs into given (daily) interval (which the posted pieces of code seem to check), are these ones:

Code:

<date> >= to_date('01-01-2009', 'dd-mm-yyyy')
  and <date> < to_date('02-01-2009', 'dd-mm-yyyy')

or

Code:

<date> between to_date('01-01-2009 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
  and to_date('01-01-2009 23:59:59', 'dd-mm-yyyy hh24:mi:ss')

Yes, you may also use (for condition on one day)

Code:

trunc(<date>) = to_date('01-01-2009', 'dd-mm-yyyy')

, anyway it is not recommended as it cannot use index on <date> column (if available).

Littlefoot · #3 (**permalink**) 07-02-09, 08:59

TRUNC will remove "time" component from a date value:

Code:

SQL> select sysdate, trunc(sysdate) from dual;

SYSDATE             TRUNC(SYSDATE)
------------------- -------------------
02.07.2009 13:57:33 02.07.2009 00:00:00

TO_DATE of a DATE doesn't make sense - why would you convert date to a date?

TO_CHAR of a date is used to format DATE value accordingly to your needs:

Code:

SQL> select to_char(sysdate, 'dd-mon-yyyy hh24:mi') from dual;

TO_CHAR(SYSDATE,'
-----------------
02-jul-2009 13:59

sunsail · #4 (**permalink**) 07-02-09, 09:01

Normally one column in the table has timestamp(6) type,and If I query this table I do this

select...
where
trunc(create_date)=trunc('17-04-2009'

however this does not work,I know there is data in the table with this date entry.

Thanks

sunsail · #5 (**permalink**) 07-02-09, 09:02

I have timestamp(6) type field ,here one example,

crea_date=('17-APR-09 02.34.26.637000000 PM)

I m querying data of "17 april" how can I do this?

select ....
where
create_date='17-05-2009'

I tried this
to_date(create_date,'dd-mm-yyyy')='17-05-2009'

Thanks

flyboy · #6 (**permalink**) 07-02-09, 09:18

When comparing two values, keep one basic rule: always compare two values with the same type. Otherwise, Oracle implicit conversion may come to place with sometimes surprising result. Preferably, compare DATE with DATE (or TIMESTAMP with TIMESTAMP in your case).

Second rule: when using Oracle built-in functions, pass parameters having exactly the same type they support. They are described in SQL Reference book, available with other documentation e.g. online on http://tahiti.oracle.com/. Please, read description of these functions (TO_TIMESTAMP, TO_DATE, TO_CHAR, TRUNC) before using them.

Did you read my post? Simply replace TO_DATE with TO_TIMESTAMP (as <date> column has TIMESTAMP data type) and use any of the first two you like more.

sunsail · #7 (**permalink**) 07-02-09, 09:26

I tried

where
cstep.creation_date=to_timestamp('17-APR-09 02.34.26.637000000 PM','DD-MON-YY HH12.MI.SS.SSSSS PM')

I m getting ora-01855 error.

flyboy · #8 (**permalink**) 07-02-09, 09:48

Quote:

Originally Posted by flyboy

Please, read description of these functions (TO_TIMESTAMP, TO_DATE, TO_CHAR, TRUNC) before using them.

They use format masks which are also described in the SQL Reference book.

'PM' is not valid format mask - AM/PM specification is represented by 'AM' mask.

Ready, fire, aim (or trial and error) method is not the best way to develop.
Maybe you shall study the documentation first.

Littlefoot · #9 (**permalink**) 07-02-09, 09:54

Right; there's a book about a Spanish guy named Manual. You should read it.

sunsail · #10 (**permalink**) 07-02-09, 10:02

Quote:

Originally Posted by flyboy

They use format masks which are also described in the SQL Reference book.

'PM' is not valid format mask - AM/PM specification is represented by 'AM' mask.

Ready, fire, aim (or trial and error) method is not the best way to develop.
Maybe you shall study the documentation first.

here it says somwthinh wrong then?
Oracle/PLSQL: To_Date Function

I m trying this
http://www.dbasupport.com/forums/arc...p/t-46511.html

flyboy · #11 (**permalink**) 07-02-09, 12:06

Thank you for the link about format masks. You might be interested in these ones:

Quote:

SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.

Can you explain, why you used 'SS.SSSSS'? Instead of the fact it is duplicated, it is also inconsistent (as 02.34.26 PM = 52466 seconds after midnight).
Did you not want to use FF format mask instead? Something like

Code:

SQL> select to_timestamp('17-APR-09 02.34.26.637000000 PM','DD-MON-YY HH12.MI.SS.FF9 PM') from dual;


TO_TIMESTAMP('17-APR-0902.34.26.637000000PM','DD-MON-YYHH12.MI.SS.FF9PM')
---------------------------------------------------------------------------
17-APR-09 02.34.26.637000000 PM

1 row selected.

SQL>

sunsail · #12 (**permalink**) 07-06-09, 09:37

Quote:

Originally Posted by flyboy

Thank you for the link about format masks. You might be interested in these ones:
Can you explain, why you used 'SS.SSSSS'? Instead of the fact it is duplicated, it is also inconsistent (as 02.34.26 PM = 52466 seconds after midnight).
Did you not want to use FF format mask instead? Something like

Code:

SQL> select to_timestamp('17-APR-09 02.34.26.637000000 PM','DD-MON-YY HH12.MI.SS.FF9 PM') from dual;


TO_TIMESTAMP('17-APR-0902.34.26.637000000PM','DD-MON-YYHH12.MI.SS.FF9PM')
---------------------------------------------------------------------------
17-APR-09 02.34.26.637000000 PM

1 row selected.

SQL>

Hello,
I donot know why but I posted an email in this thread to epxlain situation ,and now I cannot see that post,did I mispost it?

Anyway problem is that I was querying wrong column instead of one I was supposed to query.Now I can get results with trunc function in

trunc(send_date)=trunc('01-022009')

format as I was used to get.

Sorry for taking your time and making busy.

ps:I didnot create table structure therefore I donot know why "ss.sssss" is used.

Best Regards

flyboy · #13 (**permalink**) 07-06-09, 11:34

Quote:

Originally Posted by sunsail

I donot know why but I posted an email in this thread to epxlain situation ,and now I cannot see that post,did I mispost it?

I have no idea, I received only this reply.

Quote:

Originally Posted by sunsail

Now I can get results with trunc function in

trunc(send_date)=trunc('01-022009')

format as I was used to get.

Congratulations; anyway the code you posted 'does not work' for me:

Code:

SQL> select trunc('01-022009') from dual;
select trunc('01-022009') from dual
             *
ERROR at line 1:
ORA-01722: invalid number


SQL>

Quite expected result, as TRUNC function accepts only numeric or datetime type parameters, not strings. And conversion to both types fails for '01-022009', as it does not represent any number nor date.
The only reasonable explanation is, that you post here some kind of pseudocode. Please do not do it: it is very misleading to all readers. In fact, the first thing anybody will do, is correcting this rubbish instead of finding solution(s) for your problem.

Quote:

Originally Posted by sunsail

ps:I didnot create table structure therefore I donot know why "ss.sssss" is used.

As you could see my example, I did not create any table structure too.
It is all about converting VARCHAR2 literal into TIMESTAMP. And knowing, what the string representation ('17-APR-0902.34.26.637000000PM') means.
Anyway, using 'SS.SSSSS' was the reason of the ORA-01855 error.

[edit: Added the remark about ORA-01855]

sunsail · #14 (**permalink**) 07-07-09, 09:46

Hi Flyboy

trunc(send_date)=trunc('01-022009') this is what I posted,I mistyped it.I m sorry I was trying to write quickly.But it is suprising you tried it as I wrote it.

Try this
trunc(send_date)=trunc('01-02-2009').It works in oracle11G.

You could have assumed it easily mistyping.

I got this error,my point is that I wouldnot put date data in this "ss.ssss" format,that's why I said I didnot create table structure.

Best Regards

Quote:

Originally Posted by flyboy

I have no idea, I received only this reply.

Congratulations; anyway the code you posted 'does not work' for me:

Code:

SQL> select trunc('01-022009') from dual;
select trunc('01-022009') from dual
             *
ERROR at line 1:
ORA-01722: invalid number


SQL>

Quite expected result, as TRUNC function accepts only numeric or datetime type parameters, not strings. And conversion to both types fails for '01-022009', as it does not represent any number nor date.
The only reasonable explanation is, that you post here some kind of pseudocode. Please do not do it: it is very misleading to all readers. In fact, the first thing anybody will do, is correcting this rubbish instead of finding solution(s) for your problem.

As you could see my example, I did not create any table structure too.
It is all about converting VARCHAR2 literal into TIMESTAMP. And knowing, what the string representation ('17-APR-0902.34.26.637000000PM') means.
Anyway, using 'SS.SSSSS' was the reason of the ORA-01855 error.

[edit: Added the remark about ORA-01855]

beilstwh · #15 (**permalink**) 07-07-09, 09:53

trunc('01-02-2009') ONLY works because your NLS_DATE format is mm-dd-yyyy. At my site it is dd-mon-rr. Do not get into the habit of relying on the NLS settings, if it changes or the software is at another site it will always fail. Use

to_date('01-02-2009','mm-dd-yyyy')

Because the trunc is expecting a date, it implicitly converts the string to a date and then truncs it. A very bad habit to get into.

Internet Services

Web Development

Digital Marketing

Consumer Tech