How do I get Sysdate in Informatica Certification

DATE, TIMESTAMP and format masks
Release monthAPEX versionDatabase versionCloud or on premise
November 2017allallboth

DATE or TIMESTAMP data types are used all the time; they occur in every data model and consequently every APEX application must be able to handle them. This tip contains some information on how to handle these types of data - and what to look out for as an APEX developer.

DATE or TIMESTAMP?

The Oracle database offers four types of data for storing time stamps:

  • DATE is the oldest data type used to store timestamps in the database. Contrary to what the name suggests, DATE always saves both the date and the time. Specific values ​​for year, month, day, hour, minute and second are stored internally. The SQL function SYSDATE returns the current time stamp. SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24: MI: SS'; Session changed. SQL> select sysdate, dump (sysdate) as date_bytes from dual; SYSDATE DATE_BYTES -------------------- ---------------------------- ------- 2017-11-23 23:41:08 Type = 13 Len = 8: 225,7,11,23,23,41,8,0
  • TIMESTAMP extends the DATE data type by fractions of a second. The internal storage format for the TIMESTAMP also provides for the time zone in order to be able to use it actively, however the data types TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE explained below are required. As you can see in the following example, LOCALTIMESTAMP returns the current time stamp as a TIMESTAMP data type. SQL> alter session set nls_timestamp_format = 'YYYY-MM-DD HH24: MI: SS.FF6'; Session changed. SQL> select localtimestamp, dump (localtimestamp) ts_bytes from dual; LOCALTIMESTAMP TS_BYTES --------------------------- --------------------- ------------------------------------------------ 2017- 11-24 08: 45: 41.434175 Type = 187 Len = 20: 225,7,11,24,8,45,41,0,24,252,224,25,1,0,3,0,127,1,0,0
  • TIMESTAMP WITH TIME ZONE supports the time zone, and does so explicitly. The time zone information is transferred explicitly when an instance is created and it should always be included in the output (use the correct format mask). If a TIMESTAMP WITH TIME ZONE is output without time zone information, the information is incomplete.
    There are no implicit conversions between time zones; SQL functions are available to the developer for this. Instance. To get the current time stamp, use the SQL functions SYSTIMESTAMP or CURRENT_TIMESTAMP. The former returns the system time in the database time zone, the latter in the session time zone. The following is an example of SYSTIMESTAMP. SQL> alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24: MI: SS.FF6 TZR'; Session changed. SQL> select systimestamp from dual; SYSTIMESTAMP ---------------------------------- 2017-11-23 23: 57: 04.609608 -08: 00
  • TIMESTAMP WITH LOCAL TIME ZONE implicitly supports the time zone. An instance is created without specifying a time zone; the session time zone is assumed; the output is also made in the session time zone. Internally, the instances are normalized to the database time zone.
    The following example shows how TIMESTAMP WITH LOCAL TIME ZONE works. First a table is created and the current time stamp is saved in it. SYSTIMESTAMP returns a TIMESTAMP WITH TIME ZONE, but this is automatically converted to TIMESTAMP WITH LOCAL TIME ZONE. If the table is then selected, the current session time zone is always used. SQL> create table timestamp (ts timestamp with local time zone); Table created. SQL> insert into timestamp values ​​(systimestamp); 1 row created. SQL> alter session set time_zone = 'Europe / Berlin'; Session altered. SQL> select * from timestamp; TS ------------------------------------------------- -------------------------- 2017-11-24 12: 55: 39.761283 SQL> alter session set time_zone = 'EST'; Session altered. SQL> select * from timestamp; TS ------------------------------------------------- -------------------------- 2017-11-24 06:55:39.761283

Format masks

Whether you are working with an application or a tool like SQL Developer or SQLPlus; every output of a DATE or TIMESTAMP instance is a conversion to VARCHAR2. You either make it explicit with TO_CHAR - or the database makes it implicit. And for this the NLS date format masks are important. There are different NLS parameters for the default format mask for the different data types.

  • NLS_DATE_FORMAT for DATE
  • NLS_TIMESTAMP_FORMAT for TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE
  • NLS_TIMESTAMP_TZ_FORMAT for TIMESTAMP WITH TIME ZONE

In the case of an explicit conversion with TO_CHAR, the format mask can be given as a second parameter - also when converting a VARCHAR into a DATE or TIMESTAMP with TO_DATE, TO_TIMESTAMP or TO_TIMESTAMP_TZ. All details on the format masks can be found in the Oracle documentation (SQL Reference).

SQL> select to_char (sysdate, 'DD / MM / YYYY HH24-MI-SS') as sysdate_format from dual; SYSDATE_FORMAT ------------------- 24/11/2017 00-05-11 SQL> select to_date ('01 / 01/2000 08-00-00 ',' DD / MM / YYYY HH24-MI-SS ') as mydate from dual; MYDATE ------------------- 2000-01-01 08:00:00

Two conversions take place with the last command! First the string 01/01/2000 08-00-00 was converted into a DATE instance; the format mask was provided for this purpose; then it was (implicitly) converted back to VARCHAR2 for the output; the session format mask (NLS_DATE_FORMAT) is used here. When working and especially when outputting DATE or TIMESTAMP, one should always be aware of these conversions that are constantly taking place.

If no explicit format mask is used when creating DATE or TIMESTAMP instances with TO_DATE or TO_TIMESTAMP, Oracle takes the standard format mask from the session. However, this depends on the language and is therefore not stable. It is usually best to include a format mask.

The so-called DATE literal is a special case; A DATE instance can be created in the following way - without any NLS format mask and independent of the NLS setting of the database session (however, no time can be transferred).

SQL> select DATE'2017-08-01 'from dual; DATE'2017-08-01 '------------------- 01.08.2017 00:00:00

The DATE data type saves the time. If the current date is saved in a table (determined with SYSDATE), the time is always included. This behavior, in conjunction with a format mask that does not contain a time, can lead to confusing behavior ...

SQL> alter session set nls_date_format = 'DD-MON-RR'; Session altered. SQL> create table meindatum (date date); SQL> insert into meindatum (date) values ​​(sysdate); 1 row inserted. SQL> select * from meindatum; DATE --------- 24-NOV-17 1 row selected. SQL> select * from meindatum where datum = to_date ('24 -NOV-17 '); no rows selected. SQL> select * from meindatum where datum = to_date ('24 -NOV-17 ',' DD-MON-RR '); no rows selected.

Changing the date format mask makes it immediately clear.

SQL> alter session set nls_date_format = 'DD-MON-RR HH24.MI.SS'; Session altered. SQL> select * from meindatum; DATE ------------------ 24-NOV-17 00.16.19 1 row selected. SQL> select to_date ('24 -NOV-17 ',' DD-MON-RR ') from dual; TO_DATE ('24 -NOV-17 ',' DD-MON-RR ') -------------------------------- 24-NOV-17 00.00.00

You can see immediately that the date instances are not the same. If you only need the date, you should remove the time with the SQL function TRUNC before saving.

SQL> select sysdate, trunc (sysdate) from dual; SYSDATE TRUNC (SYSDATE) ------------------- ---------------------- NOV 24- 17 02.36.30 24-NOV-17 00.00.00

And Application Express ...?

Everything written up to this point also applies without restriction to Application Express. Whenever a DATE or TIMESTAMP column is used in an APEX component, the display follows the behavior described. While you change the standard format mask for DATE or TIMESTAMP in SQL Plus with the ALTER SESSION command, you set it for an APEX application in the application attributes. To do this, navigate to the Shared Components, then to the Application Definition Attributes and then to Globalization.

Set date format masks for the application - Shared Components

The format masks Application Date Format, Application Timestamp Format and Application Timestamp Time Zone Format set here are used by Application Express to change the NLS session parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT for this application. If nothing is set, the defaults of the Oracle database take effect. The Application Date Time Format plays a special role; it has no effect on an NLS parameter and is only effective at selected points in Application Express.

Wherever a DATE or TIMESTAMP is used, the date format is based on the settings made here, unless an explicit format mask is used. Explicit format masks can be stored in all possible places, for example report columns or date pickers; these then take precedence over the default.

A special format mask that is only available in Application Express is SINCE. It is already used in numerous places in the Application Builder.

The SINCE format mask in action

SINCE ensures that the date is output as text that indicates how long ago the timestamp was. The language depends on the application language; and the languages ​​are available for which translations of the Application Builder are available (the respective translation of the Application Builder must be installed). In the PL / SQL context, SINCE can be used with the APEX_UTIL.GET_SINCE function; However, SINCE cannot be used when calling TO_DATE or TO_CHAR.

In a report, SINCE, analogous to DD.MM.YYYY or DD-MON-RR, is simply entered as a format mask for a report column.

Use SINCE in your own report

DATE or TIMESTAMP is also important in APEX forms; almost all forms contain dates in some way. APEX saves every page item in the session state as VARCHAR2, regardless of whether it is displayed as a text field, number field or date picker.

This means that the rules described so far also apply to APEX form elements. If a form is loaded, i.e. the values ​​of the table row are read into the form elements, a conversion to VARCHAR2 takes place. If no explicit format mask is stored, the settings in the Application Attributes are used; if nothing is stored there, the defaults of the Oracle database apply.

This is explained using the example of a date picker form element. It is assumed that the explicit format mask DD.MM.YYYY is stored in the date picker.

Date Picker form element with explicit format mask

  • When the page is loaded, a table row is read. In this case, the HIREDATE column is selected with a TO_CHAR (HIREDATE, 'DD.MM.YYYY') so that the P22_HIREDATE element is filled with the content 02/14/1982, for example.
  • The same format mask is transferred to the Javascript library for the date picker (it has to be reformatted a little), which renders the specific date selection.
  • If the user has selected a date, the Javascript library sets the browser element with the new date - again in the format DD.MM.YYYY.
  • With the Page Submit, the date is sent to Application Express in the format DD.MM.YYYY and is also saved in the same way, as VARCHAR2, in the APEX Session State. When saving to the table, Application Express finally generates a TO_DATE ({Date}, 'DD.MM.YYYY') expression to convert the date back to a DATE.

Application Express elements are always VARCHAR2 - and conversions take place when changing to a table column or a PL / SQL variable of the type DATE or TIMESTAMP. And that is completely independent of the element type - with a text field it is the same as with a date picker. While the first simply represents the VARCHAR2 date, the latter shows a dialog based on the date and the format mask.

This should be kept in mind for all PL / SQL processes and SQL queries that you build into your applications. If you write a "Page Submit PL / SQL process" that works with an element of the Date Picker type, you should pay attention to the format mask: If there is one stored in the form element, you should also use it in the PL / SQL code.

declare l_date date; begin l_date: = to_date (: P22_HIREDATE, 'DD.MM.YYYY'); : - processing here end;

Of course, the topic of "date processing" is by no means covered by these explanations; The surface has just been touched upon with a rough distinction between data types and the explanations of format masks. Another very interesting topic is date arithmetic or working with date intervals, which, however, has to be reserved for another community tip.

Back to the community page