Oracle Date and time conversion

Here is a comprehensive guide to Oracle date and time formats, covering the fundamental data types and how to use them.

The Core Data Types

Oracle has three primary data types for storing date and time information:

  1. DATE: The classic data type. It stores both date and time down to the second. · Format: YYYY-MM-DD HH24:MI:SS (It always has these components, even if you don't see them). · Storage: It stores century, year, month, day, hour, minute, and second. It does not store fractional seconds or time zone information. · Example Storage: 2023-10-25 14:30:22
  2. TIMESTAMP: An extension of DATE that includes fractional seconds. · Format: YYYY-MM-DD HH24:MI:SS.FF · Precision: By default, FF has 6 digits of precision (microseconds). You can specify a precision from 0 to 9 (e.g., TIMESTAMP(3) for milliseconds). · Example Storage: 2023-10-25 14:30:22.123456
  3. TIMESTAMP WITH TIME ZONE: A TIMESTAMP that also stores the time zone region or time zone offset. · Format: YYYY-MM-DD HH24:MI:SS.FF TZR or YYYY-MM-DD HH24:MI:SS.FF TZH:TZM · Use Case: Essential for applications across different time zones. · Example Storage: 2023-10-25 14:30:22.123456 US/Pacific or 2023-10-25 14:30:22.123456 -07:00
  4. TIMESTAMP WITH LOCAL TIME ZONE: Similar to the above, but the time is normalized to the database's time zone for storage. When a user queries the data, it is displayed in the user's session time zone. · Use Case: Simplifies display for users in their local time, while handling conversions automatically.
  1. Formatting Dates and Times (TO_CHAR)

The TO_CHAR function is used to convert a DATE or TIMESTAMP into a formatted string.

Syntax:

TO_CHAR(date_column, 'format_model')

Common Format Models:

Format Element Description Example Output YYYY 4-digit year 2023 YY 2-digit year 23 MM 2-digit month 10 MON Abbreviated month name OCT MONTH Full month name OCTOBER DD 2-digit day of month 25 DY Abbreviated day name WED DAY Full day name WEDNESDAY HH24 Hour (00-23) 14 HH or HH12 Hour (01-12) 02 MI Minute (00-59) 30 SS Second (00-59) 22 FF Fractional Seconds (for TIMESTAMP) 123456 AM or PM Meridian indicator PM TZH Time zone hour offset -07 TZM Time zone minute offset 00 / . , - Punctuation and separators as used

Examples of TO_CHAR:

SELECT
  TO_CHAR(SYSDATE, 'MM/DD/YYYY') AS "Simple Date",           -- 10/25/2023
  TO_CHAR(SYSDATE, 'Dy, Mon DD, YYYY') AS "Text Date",       -- Wed, Oct 25, 2023
  TO_CHAR(SYSDATE, 'HH24:MI:SS') AS "Military Time",         -- 14:30:22
  TO_CHAR(SYSDATE, 'HH:MI:SS AM') AS "12-Hour Time",         -- 02:30:22 PM
  TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH24:MI:SS.FF3') AS "With Milliseconds" -- 25-OCT-2023 14:30:22.123
FROM dual;
  1. Converting Strings to Dates/Timestamps (TO_DATE, TO_TIMESTAMP)

The TO_DATE and TO_TIMESTAMP functions convert a string into a DATE or TIMESTAMP data type.

Syntax:

TO_DATE(string_column, 'format_model')
TO_TIMESTAMP(string_column, 'format_model')

Examples:

-- Converting a string to a DATE
INSERT INTO orders (order_date)
VALUES (TO_DATE('25-OCT-2023 02:30 PM', 'DD-MON-YYYY HH:MI AM'));

-- Converting a string with a custom format to a TIMESTAMP
SELECT TO_TIMESTAMP('2023/10/25 14:30:22.123', 'YYYY/MM/DD HH24:MI:SS.FF3')
FROM dual;

-- Converting a string with time zone
SELECT TO_TIMESTAMP_TZ('2023-10-25 14:30:22 US/Pacific', 'YYYY-MM-DD HH24:MI:SS TZR')
FROM dual;

Crucial Note: If you don't specify a format model for a string that doesn't match the NLS_DATE_FORMAT of your database, you will get an error. Always use an explicit format model for reliability.

  1. Default Display and NLS Settings

The way Oracle displays dates by default is controlled by the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT parameters. This is a common source of confusion.

· View your current settings:

  SELECT * FROM NLS_SESSION_PARAMETERS
  WHERE PARAMETER IN ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_TIME_FORMAT');

· Change the format for your session:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
  ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';

After running this, a simple SELECT SYSDATE FROM dual; will show the full date and time.

Summary and Best Practices

Your Goal Function to Use Example Display a date/time as a formatted string TO_CHAR TO_CHAR(order_date, 'MM/DD/YYYY') Convert a string into a DATE TO_DATE TO_DATE('10/25/2023', 'MM/DD/YYYY') Convert a string into a TIMESTAMP TO_TIMESTAMP TO_TIMESTAMP('2023-10-25 14:30:22.123', '...') Get the current date and time SYSDATE (for DATE) SYSTIMESTAMP (for TIMESTAMP) SELECT SYSTIMESTAMP FROM dual;

Key Takeaway: Never rely on implicit conversions. Always use TO_DATE, TO_TIMESTAMP, and TO_CHAR with an explicit format model to ensure your code is predictable, correct, and independent of session-level NLS settings.

Comments

Post a Comment

Popular posts from this blog

Terminal-Based Writing

Terminal-Based Writing: From Termux to Professional Docs (A Beginner’s Guide to WordGrinder + Pandoc) Do you love writing in the terminal but need professional-looking PDFs or Word files? This guide will show you how to: Write in Termux using wordgrinder (a simple terminal word processor). Convert to Markdown for flexibility. Generate beautiful PDFs/DOCX files using pandoc . No complex setups—just clear, step-by-step instructions. Let’s begin! Step 1: Install Termux & Tools (Skip if you already have Termux!) Install Termux (Android terminal emulator). Update packages: pkg update && pkg upgrade Install WordGrinder (terminal word processor) and Pandoc (document converter): pkg install wordgrinder pandoc Step 2: Write in WordGrinder WordGrinder is a lightweight word processor with bold, italics, and underlines . Basic Commands Open a new file: wordgrinder my_note.zws Shortcuts: Ctrl+S → Save Ctrl+B → Bold Ctrl+I → Italics...
Read more

Markdown Quick Tutorial

Markdown Quick Tutorial: The Essentials in 5 Minutes Markdown is a lightweight markup language for formatting plain text. It’s perfect for notes, docs, and even websites (like GitHub READMEs). Here’s all you need to know: 1. Basic Formatting Style Markdown Output Bold **bold** bold Italic *italic* italic Strikethrough ~~strike~~ strike Code `code` code Link [text](URL) Google Image ![alt](image.png) (Shows image) 2. Headings # Heading 1 (Biggest) ## Heading 2 ### Heading 3 #### Heading 4 3. Lists Bullet Points - Item 1 - Item 2 - Sub-item (indent with 2 spaces) Numbered Lists 1. First 2. Second 3. Third 4. Code Blocks Inline Code Use `print("Hello")` in Python. Multi-line Code Use triple backticks + language name: ```python def hello(): print("World") ``` (Shows with syntax highlighting!) 5. Tables | Name | Age | Job | |---------|-----|----------| | Alice | 25 | Engineer | | Bob | 30...
Read more

To install Jupyter Notebook on Ubuntu (Linux)

To install Jupyter Notebook on Ubuntu 24, follow these steps: Update the system : sudo apt update && sudo apt upgrade -y Install Python and pip (Ubuntu 24 typically comes with Python pre-installed): sudo apt install python3 python3-pip -y Install Jupyter Notebook : pip3 install jupyter Verify the installation : jupyter notebook --version Launch Jupyter Notebook : jupyter notebook This will start the Jupyter server and open a browser window. If it doesn’t, navigate to http://localhost:8888 in your browser. Optional: Install in a virtual environment (recommended to avoid package conflicts): Install virtualenv: sudo apt install python3-venv -y Create and activate a virtual environment: python3 -m venv jupyter_env source jupyter_env/bin/activate Install Jupyter Notebook inside the virtual environment: pip install jupyter Run Jupyter Notebook as above. Notes : Ensure you have an active internet connection. If you encounter permission issues with pi...
Read more