SQL working with dates

 How to use Dates, Timestamps and intervals in Oracle databases.

Data types available:

• Date
• Timestamp
• Timestamp
• Timestamp with time zone
• Timestamp with local time zonr
• Interval
• Year to month
• Day to second

Date:

The DATE datatype is used by Oracle to store all datetime information where a precision greater than 1 second is not needed. Oracle uses a 7 byte binary date format which allows Julian dates to be stored within the range of 01-Jan-4712 BC to 31-Dec-9999 AD.

Example (10-JUL-2004 17:21:30)

Date functions:

SYSDATE:

Returns the current date-time from the operating system of the database server.

SELECT SYSDATE 
FROM   dual;

SYSDATE
-------------------
10/07/2004 18:34:12

1 row selected.

CURRENT_DATE

ADD_MONTHS(date, months)

LAST_DAY(date)

MONTHS_BETWEEN(date, date)

NEXT_DAY(date, day)

NEW_TIME(date, timezone1, timezone2)

TO_CHAR(date, format):

Converts a specified date to a string using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used.

SELECT TO_CHAR(SYSDATE, 'DD/MM/YY HH24:MI') 
FROM   dual;

TO_CHAR(SYSDAT
--------------
10/07/04 18:48

1 row selected.

For different parameters or format mask:

https://www.techonthenet.com/oracle/functions/to_char.php

TO_DATE(date_string, format):

Converts a specified string to a date using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used.

SELECT TO_DATE('10/07/2004 13:31:45', 'DD/MM/YYYY HH24:MI:SS') 
FROM   dual;

TO_DATE('10/07/2004
-------------------
10/07/2004 13:31:45

1 row selected.

ROUND(date, format):

Returns a date rounded to the level specified by the format. The default value for the format is DD, returning the date without the fractional (time) component, making it represent midnight on the specified date, or the following date depending on the rounding.

SELECT SYSDATE, 
       ROUND(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             ROUND(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:54:24 10/07/2004 19:00:00

1 row selected.

TRUNC(date, format):

Returns a date truncated to the level specified by the format. The default value for the format is DD, truncating the fractional (time) component, making it represent midnight on the specified date. Using the TRUNC function allows comparison of dates without the time components distracting from the true meaning of the comparison. It is similar to the round function, except that it always rounds down.

SELECT SYSDATE, 
       TRUNC(SYSDATE, 'HH24') 
FROM   dual;

SYSDATE             TRUNC(SYSDATE,'HH24
------------------- -------------------
10/07/2004 18:55:44 10/07/2004 18:00:00

1 row selected.

TIMESTAMP

The TIMESTAMP datatype is an extension on the DATE datatype. In addition to the datetime elements of the DATE datatype, the TIMESTAMP datatype holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6. There are also two variants called TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. As their names imply, these timestamps also store time zone offset information.

Example (10-JUL-2004 17:21:30.662509 +01:00)

Select ...

From ...

Where datetime_col > to_date('16-Feb-2023', 'DD-Mon-YYYY')

Reference:

https://oracle-base.com/articles/misc/oracle-dates-timestamps-and-intervals

https://youtu.be/P0qVs5ukKUk?si=Kt6WUk3sz1-tIz00