Oracle TRUNC function doesn't work in perl

Question

I try to insert the created field in my_table. The created field has a datetime type. In my_table the field my_created has a date format. So I try to TRUNC the created field. However I'm getting the error ORA-01830: date format picture ends before converting entire input stringwhile inserting the truncated value. It seems, that the time is still there but is reset to 00:00. how can I get only the date without time? It happens only in perl. I'm getting only date in toad. Very simplified code looks like:

my $SQL="SELECT 
        TRUNC(CREATED),
    FROM 
        DBA_OBJECTS";
my $sth = $db->prepare($SQL);
$sth->execute();
my $date =  $sth->fetchrow();

$SQL = "INSERT INTO  MY_TABLE
                    (MY_CREATED) 
         VALUES (?)";
        
my $stmt = $dbh_master->prepare($SQL);
$stmt->execute($date);

EDIT: I found an ugly workaround and I'm executing it like this:

$stmt->execute(substr($date, 0, 10));

However maybe someone has a nicer solution.

Answer 1

How can I get only the date without time?

In Oracle, a DATE is a binary data type that is composed of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those binary components so if you want an Oracle DATE data type then you cannot get it without a time.

The Oracle DATE data type was released with Oracle version 2 in 1979 and predates the ANSI/ISO standard (of 1986, where a DATE does not have a time component) and Oracle has maintained backwards compatibility with their previous data types rather than adopting the ANSI standard.

If you use the TRUNC(date_value, format_model) function then it will set the binary components of the DATE, up to the specified format model, to the minimum (0 for hours, minutes and seconds, 1 for days and months) but it will NOT give you a data type that does not have a time component.

---

It happens only in perl. I'm getting only date in toad.

No, you are getting the entire 7 byte binary value in Toad; however, the user interface is only choosing to show you the date component. There should be a setting in the preferences that can set the date format in Toad which will let you see the entire date-time components.

Oracle SQL/Plus and SQL Developer use the NLS_DATE_FORMAT session parameter and Toad may also be able to use that.

---

If you want to get the value as a DATE then it will always have a time component (even if you set that time component to zeros using TRUNC).

If you want to get the date so that it is formatted in a way without a time component then you need to convert it to another data type and can use TO_CHAR to format it as a string:

SELECT TO_CHAR(CREATED, 'YYYY-MM-DD')
FROM   DBA_OBJECTS

But then you will be returning a (formatted) string and not a DATE data type.