SQL Date and Time Functions: Tips, Tricks, and Examples

MySQL

Aug 13, 2025Aug 17, 2025

Working with SQL date and time functions is an essential skill for any database developer or analyst—but with a little know-how, it becomes one of the most powerful aspects of database handling. Whether you’re tracking events, slicing up historical data, or building reports, mastering these functions will level up your SQL game.

Contents show

In this guide, you’ll discover essential tricks, hacks, and tips to simplify working with dates and times across different SQL dialects.

SQL Date and Time Data Types

Before you dive into the functions, let’s look at the core date/time data types:

DATE: Stores only a date (e.g., 2025-08-17).
TIME: Stores only a time (e.g., 14:39:00).
TIMESTAMP: Stores both date and time (e.g., 2025-08-17 14:39:00).

Each type has specific use cases. Choose the type that matches your data and query needs!

How to get the Current Date and Time in SQL

Fetching the system’s current date and time is a common requirement. SQL provides built-in functions, size-tuned for your database:

Oracle: SQL SELECT SYSDATE FROM dual;
Standard SQL / Other RDBMS: SELECT CURRENT_TIMESTAMP;

Related read: How to Install PHP: A Beginner’s Guide

Generate Date Series in SQL

Ever wanted to generate a list of dates, such as every day in a month or all Mondays in a quarter? Here’s how:

Generate a Sequence of Dates (Oracle)

SELECT 
  TRUNC(SYSDATE) + LEVEL - 1 AS dt
FROM 
  dual
CONNECT BY
  LEVEL <= 10;
-- This generates 10 consecutive dates from today onwards

Generate First Day of Last 12 Months (Oracle)

SELECT 
  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -LEVEL + 1) AS first_day
FROM 
  dual
CONNECT BY
  LEVEL <= 12;

Find Specific Days (e.g., All Mondays Since a Date)

SELECT 
  your_date_column
FROM 
  your_table
WHERE 
  TO_CHAR(your_date_column, 'DY') = 'MON'
  AND your_date_column >= TO_DATE('2024-01-01', 'YYYY-MM-DD');

(Adjust for your RDBMS and locale!)

Advanced Date and Time Techniques in SQL

Get the First and Last Day of a Month

First day of the current month:

SELECT TRUNC(SYSDATE, 'MM') AS first_day FROM dual;

Last day of the current year:

SELECT TRUNC(LAST_DAY(SYSDATE), 'YYYY') + INTERVAL '1' YEAR - INTERVAL '1' DAY AS last_day FROM dual;

Number of Days in the Current Month

SELECT EXTRACT(DAY FROM LAST_DAY(SYSDATE)) AS days_in_month FROM dual;

Difference Between Two Dates

Get the difference in days:

SELECT (DATE2 - DATE1) AS days_difference FROM dual;

(Replace DATE1 and DATE2 with your actual date columns or values.)

Practical Tips for Real-World SQL

Handle Time Zones

Dealing with users or systems from different regions? Always manage time zones correctly:

SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC' FROM dual;

Formatting Date and Time in SQL for Display

Use TO_CHAR to format your dates however you like:

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

More Real-Life Examples for SQL date and time functions

Here are some handy patterns for everyday date manipulation:

Add Days:

SELECT SYSDATE + 7 AS one_week_later FROM dual;

Subtract Months:

SELECT ADD_MONTHS(SYSDATE, -1) AS last_month FROM dual;

Extract Year/Month:

SELECT EXTRACT(YEAR FROM SYSDATE) AS current_year FROM dual;

Conclusion

Learning to wrangle dates and times in SQL is a journey, but once you master these patterns, you’ll handle database time data with confidence. Play around, experiment with different functions, and—above all—practice in your own SQL environment to internalize these skills.

You’re now equipped with a toolkit of tricks and hacks for making your SQL date/time queries clean, efficient, and powerful. Happy querying!

Share this article