CODEWITHSUNDEEP
oracle-databaseoracle11g
Dipali Y.
Dipali Y.

Reputation: 113

how to use trunc date() function in oracle.?

When I run following query I got error.Can Someone help me to solve this.

 select trunc('27-oct-97','dd-mm-yy') form dual;

when I run this query i got following error:

Error:inline number function.

Upvotes: 1

Views: 38448

Answers (2)

MT0
MT0

Reputation: 167867

The TRUNC function has the signature:

TRUNC( date_value, format )

You are providing a string value instead of a date value and 'dd-mm-yy' is an invalid format (you just want to truncate to the start of the day using 'dd' as the format or the start of the month using 'mm' or the start of the year using 'yy' - but using all three together does not make sense).

You can use ANSI date literals to specify the date:

SELECT TRUNC(
         DATE '1997-10-27',
         'DD'
       )
FROM   DUAL

Or use the TO_DATE function:

SELECT TRUNC(
         TO_DATE( '27-oct-97', 'DD-MON-RR' ),
         'DD'
       )
FROM   DUAL

(and you can also omit the 'DD' format argument as the default is to truncate to the start of the day.)

But, in both cases, the truncation is redundant as the date's time component is already midnight so does not need truncating.

What you want is a date value that has a non-midnight time component:

SELECT TRUNC(
         TO_DATE( '1997-10-27 12:34:56', 'YYYY-MM-DD HH24:MI:SS' ),
         'DD'
       )
FROM   DUAL

or to truncate to the start of the month or year:

SELECT TRUNC(
         DATE '1997-10-27',
         'MM'
       )
FROM   DUAL

Upvotes: 5

sagi
sagi

Reputation: 40481

You have two options:

1) Remove the time portion from the date truncate into the nearest date:

TRUNC(DATE)

2) Use format, to specify which values to trunc:

TRUNC(DATE,year)

Truncates (will turn to 1) everything that is before the format , in this example the year. So this:

trunc(to_date('27-oct-97','dd-mm-yy'),'YEAR')

Will output this:

01-jan-97

Upvotes: 1

Related Questions