How to set the display format for PL/SQL date?

2 years ago
Olivia Parker
2 minutes

In PL/SQL, the TO_CHAR function can be used to set the display format of a date. The syntax of the TO_CHAR function is as follows:

Format the date value to a specific format.

In this case, “date_value” is the date value to be formatted and “format” is the display format of the date.

Here are some commonly used date display formats:

  1. YYYY-MM-DD HH24:MI:SS: It displays the year, month, day, hour, minute, and second, such as 2022-01-01 12:30:00.
  2. YYYY-MM-DD: Displays the year, month, and date, such as 2022-01-01.
  3. Displaying the date, month, year, hours, minutes, and seconds as 01-JAN-2022 12:30:00.
  4. MON DD, YYYY: Displays the month, date, and year, such as JAN 01, 2022.
  5. HH24:MI:SS displays the hour, minutes, and seconds, such as 12:30:00.

Here are some examples:

DECLARE
  my_date DATE := SYSDATE;
BEGIN
  -- 显示年份、月份、日期、小时、分钟和秒
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_date, 'YYYY-MM-DD HH24:MI:SS'));

  -- 显示年份、月份和日期
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_date, 'YYYY-MM-DD'));

  -- 显示日期、月份、年份、小时、分钟和秒
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_date, 'DD-MON-YYYY HH24:MI:SS'));

  -- 显示月份、日期和年份
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_date, 'MON DD, YYYY'));

  -- 显示小时、分钟和秒
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(my_date, 'HH24:MI:SS'));
END;

Please note that the DBMS_OUTPUT.PUT_LINE statement in the above example is used to output results in PL/SQL. You can adjust the date format to suit your application as needed.

bannerAds