Oracle SELECT查询中日期的正确格式化方法及SYSDATE指定格式输出方案

Got it, let's break down how to properly format dates in Oracle SQL SELECT queries, plus how to format SYSDATE specifically.

一、SELECT查询中日期的正确格式化方式 #

First off, it's important to remember: Oracle date/timestamp types don't have an inherent display format—the format you see is just how your client (like SQL*Plus, SQL Developer) renders it by default. To control the output format explicitly, you need to use the TO_CHAR() function, which converts a date value to a string using a custom format model.

Here's the basic syntax:

TO_CHAR(date_column_or_value, 'format_model')

Common format model components you'll use: #

• YYYY: 4-digit year (e.g., 2024)
• YY: 2-digit year (e.g., 24)
• MM: 2-digit month (e.g., 03 for March)
• MON: Abbreviated month name (e.g., MAR, depends on NLS settings)
• MONTH: Full month name (e.g., MARCH)
• DD: 2-digit day of the month (e.g., 15)
• DY: Abbreviated day name (e.g., FRI)
• DAY: Full day name (e.g., FRIDAY)
• HH24: 24-hour format hour (e.g., 14 for 2 PM)
• HH12: 12-hour format hour (e.g., 02 for 2 PM)
• MI: 2-digit minutes
• SS: 2-digit seconds

Example usage with a date column: #

Suppose you have a table orders with a order_date column (DATE type). To select it in YYYY-MM-DD format:

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_order_date
FROM orders;

If you want a more readable format with time:

SELECT TO_CHAR(order_date, 'DD-MON-YYYY HH24:MI:SS') AS order_datetime
FROM orders;

二、将SYSDATE按指定格式输出的操作 #

SYSDATE is Oracle's built-in function that returns the current date and time of the database server. Formatting it works exactly the same way as any other date value—wrap it in TO_CHAR() with your desired format model.

Basic examples: #

1. Get current date in YYYY/MM/DD format:

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') AS current_date
FROM dual;

2. Get current date and time in 24-hour format:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS current_datetime
FROM dual;

3. Format with localized month/day names (e.g., Chinese):
   If you want to display month/day names in a specific language, you can add the NLS_DATE_LANGUAGE parameter to the TO_CHAR() function:

SELECT TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日" HH24:MI:SS', 'NLS_DATE_LANGUAGE=SIMPLIFIED CHINESE') AS current_datetime_cn
FROM dual;

This will output something like 2024年05月20日 16:30:45.

A quick note: #

If you ever need to do the reverse—convert a formatted string back to a date—use TO_DATE() with the matching format model. But for outputting dates in your desired format, TO_CHAR() is the way to go.

内容的提问来源于stack exchange，提问作者Sai Nikhil