CODEWITHSUNDEEP
oracle-databasedateplsqltoad
MySqlDev788
MySqlDev788

Reputation: 13

Toad dont return me the time

I got a problem with my date conversion , when I do this part of code , it return me only the date without the time :

DECLARE
        vdate VARCHAR2(255);
BEGIN  
        select cast(TO_TIMESTAMP_TZ('2020-09-23T03:02:00+02:00','yyyy-mm-dd"T"hh24:mi:ss"+"TZH:TZM') as date) into vdate from dual; 
        DBMS_OUTPUT.PUT_LINE(vdate);
END;

This return me only :

23-SEP-20

How can I do to get date and hours ? Thanks

Upvotes: 0

Views: 865

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6094

In Toad, you can set the Date format mask under View -> Toad Options. My screenshot is from Toad 13.2.0.258.

enter image description here

Upvotes: 0

MT0
MT0

Reputation: 168106

DECLARE
  vdate VARCHAR2(255);
BEGIN  
  select cast(
           TO_TIMESTAMP_TZ(
             '2020-09-23T03:02:00+02:00',
             'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM' -- remove the "+"
           )
           as date
         )
  into   vdate
  from   dual;

  DBMS_OUTPUT.PUT_LINE(vdate);
END;
/

You go from a string (2020-09-23T03:02:00+02:00) to a timestamp (using the TO_TIMESTAMP_TZ function, and you shouldn't explicitly match the + character and should, instead, let the TZH format model match it) then to a date and then you put it into a string variable which requires an implicit conversion from the binary date value to a string. This all seems overkill. However, it is this last step, the implicit conversion from date to string, that is causing your issue as Oracle will use the NLS_DATE_FORMAT session parameter as the format model to do the conversion.

One method of solving this is to use:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS';

and then your code should output the correct value without having to change your code.

However, you should not use this method as it relies on the user changing their session and most users aren't going to do that (and if they do then they are just as likely to change it to a date format that they prefer than to the one you want which would, again, break your expectations for the code's behaviour).

Instead, if you want a specific format or a date then you should explicitly apply the format using TO_CHAR:

BEGIN  
  DBMS_OUTPUT.PUT_LINE(
    TO_CHAR(
      TO_TIMESTAMP_TZ(
        '2020-09-23T03:02:00+02:00',
        'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM'
      ),
      'YYYY-MM-DD"T"HH24:MI:SS'
    )
  );
END;
/

(Note: you don't need to: declare a variable; to use SQL; or to convert to a date. Instead you can do it all in the one line.)

If you did want to use a variable then you can do it all in PL/SQL by assigning the value directly to the variable (and don't need to context-switch to use the SQL engine):

DECLARE
  vdate DATE;
BEGIN
  vdate := TO_TIMESTAMP_TZ(
             '2020-09-23T03:02:00+02:00',
             'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM'
           );

  DBMS_OUTPUT.PUT_LINE( TO_CHAR( vdate, 'YYYY-MM-DD"T"HH24:MI:SS' ) );
END;
/

db<>fiddle here

Upvotes: 1

Related Questions