CODEWITHSUNDEEP
sqlperloracle-database
David
David

Reputation: 21

Perl and Oracle DB - help

I am using Oracle express database, and I would like to know how can I change the date formatting- from dd-mm-yyyy to dd-mm-yyyy hh-mm. Also, I've heard something about alter session, but I don't know how to use it in Perl.

This is what I did so far:

my $sth = $dbh->prepare("INSERT INTO Perl 
                           (A_FIELD,B_FIELD,C_FIELD,TIME_STAME) 
                         VALUES 
                           (?,?,?,TO_DATE(?,'DD/MM/YYYY HH24:MI'))");

Upvotes: 1

Views: 1827

Answers (2)

DCookie
DCookie

Reputation: 43533

Date fields in Oracle are not formatted for display - it's an internal format that you convert to/from on input/output. When you store a date in Oracle date datatype columns, you convert your character string to internal format by describing the date-time to the TO_DATE function with the format model string. Oracle interprets the character string to it's internal format. When you need to display the date, you do the reverse - you tell oracle how to display the date by again giving a format model, this time to the TO_CHAR function.

To illustrate with your example, you could convert dd-mm-yyyy to dd-mm-yyyy hh-mm without ever storing the value (I assume you meant to display hours-minutes. The format model for minutes is 'MI', since 'MM' is month):

SQL> SELECT TO_CHAR(TO_DATE('01-01-2020','DD-MM-YYYY'),'DD-MM-YYYY HH-MI') mydate
      FROM DUAL;

MYDATE
----------------
01-01-2020 12-00

Note that with your example, the time portion of your date is not supplied on input, so it defaults to midnight. To store a time value in your date column, you must supply a time value in your input:

SQL> SELECT TO_CHAR(TO_DATE('01/01/2020 10:13','DD/MM/YYYY HH:MI'),'DD-MM-YYYY HH-MI') mydate
        FROM DUAL;

MYDATE
----------------
01-01-2020 10-13

SQL>

Depending on what you're trying to do, the system date in Oracle can be obtained by a reference to the pseudo-column SYSDATE:

SQL> SELECT TO_CHAR(sysdate,'MM/DD/YYYY HH:MI:SS AM') dt1,
  2         TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS') dt2
  3    FROM dual;

DT1                    DT2
---------------------- -----------------------------
07/01/2011 03:44:30 PM 01-JUL-2011 15:44:30

SQL>

So the roundabout answer to your question is that it entirely depends on what format your input date string is in. You convert that to Oracle's date type via a format model and the TO_DATE function, then convert the date item to a display format of your choosing via TO_CHAR and a format model. As for the "ALTER SESSION" command you alluded to in your question, you can specify a default format model for date conversions by specifying the NLS_DATE_FORMAT parameter in the ALTER SESSION command:

SQL> SELECT sysdate FROM dual;

SYSDATE
---------
02-JUL-11

SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL> SELECT sysdate FROM dual;

SYSDATE
--------------------
02-jul-2011 10:39:24

Upvotes: 3

Chandu
Chandu

Reputation: 82943

If the incoming date string is in mm-yyyy format, then you can use the statement below(TO_DATE(?,'MM-YYYY')) to convert the string to date:

$sth = $dbh->prepare("INSERT INTO Perl (A_FIELD,B_FIELD,C_FIELD,TIME_STAME) VALUES (?,?,?,TO_DATE(?,'MM-YYYY'))");

Upvotes: 0

Related Questions