What is the difference between GETDATE and SYSDATETIME?

Both return the current time in SQL Server; GETDATE returns datetime (3 ms precision), SYSDATETIME returns datetime2 (100 ns). Prefer SYSDATETIME in new code, though either works for reporting.

Why does MySQL have no date_trunc?

MySQL never added it; the community uses the DATE_FORMAT first-day pattern. Recent MariaDB versions have similar functions, but stick to the pattern if you want portability.

How do I write a date literal in Oracle?

The safest is the ANSI form: DATE '2026-01-01'. TO_DATE('01/01/2026','DD/MM/YYYY') works too, but avoid formats that depend on session settings.

Do I have to memorize these differences?

You do not; that is exactly why this page exists. One step further is not thinking about the dialect at all: type your question into PerSight in plain language, and it picks the date functions matching whichever engine you are connected to, showing you the SQL it generated.

← All postsThe PerSight Team · Last updated: July 25, 2026 · 8 min read

SQL Date Functions Compared: PostgreSQL, SQL Server, MySQL, and Oracle

Today's date, going back 30 days, truncating to month, extracting year and month, date differences, and formatting: the 6 most-needed date operations, side by side in all four database dialects.

Date functions are where SQL dialects diverge the most. A query written for PostgreSQL breaks on Oracle, and one that works on SQL Server returns something else on MySQL. This page is a reference that puts the six most-needed operations side by side in all four engines' own syntax; it was written to be bookmarked.

1. Today's date and the current time

-- PostgreSQL
SELECT CURRENT_DATE, NOW();

-- SQL Server
SELECT CAST(GETDATE() AS date), GETDATE();

-- MySQL
SELECT CURDATE(), NOW();

-- Oracle
SELECT TRUNC(SYSDATE), SYSDATE FROM dual;

Oracle requires FROM dual; TRUNC(SYSDATE) zeroes the time part to give you "today".

2. Adding and subtracting days or months

-- PostgreSQL
SELECT CURRENT_DATE - INTERVAL '30 days';
SELECT CURRENT_DATE + INTERVAL '3 months';

-- SQL Server
SELECT DATEADD(DAY, -30, CAST(GETDATE() AS date));
SELECT DATEADD(MONTH, 3, CAST(GETDATE() AS date));

-- MySQL
SELECT DATE_SUB(CURDATE(), INTERVAL 30 DAY);
SELECT DATE_ADD(CURDATE(), INTERVAL 3 MONTH);

-- Oracle
SELECT TRUNC(SYSDATE) - 30 FROM dual;
SELECT ADD_MONTHS(TRUNC(SYSDATE), 3) FROM dual;

In Oracle, adding days is plain arithmetic (date + number); adding months needs ADD_MONTHS.

3. Truncating to the start of month or year

This is the foundation of "since the start of this month" filters, and the four engines take four different roads:

-- PostgreSQL
SELECT date_trunc('month', CURRENT_DATE);

-- SQL Server (2022+)
SELECT DATETRUNC(month, GETDATE());
-- SQL Server (older versions)
SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);

-- MySQL (no built-in trunc; first-day pattern)
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01');

-- Oracle
SELECT TRUNC(SYSDATE, 'MM') FROM dual;

For start of year: date_trunc('year',...), DATETRUNC(year,...), '%Y-01-01', TRUNC(SYSDATE, 'YYYY').

4. Extracting year, month, and day

-- PostgreSQL / Oracle (standard)
SELECT EXTRACT(YEAR FROM order_date),
       EXTRACT(MONTH FROM order_date)
FROM orders;

-- SQL Server
SELECT YEAR(order_date), MONTH(order_date), DAY(order_date)
FROM orders;

-- MySQL (both work)
SELECT YEAR(order_date), EXTRACT(MONTH FROM order_date)
FROM orders;

5. The difference between two dates

-- PostgreSQL (date - date = number of days)
SELECT delivery_date - order_date AS days_between FROM orders;

-- SQL Server (careful: unit first, then the earlier date)
SELECT DATEDIFF(DAY, order_date, delivery_date) FROM orders;

-- MySQL (careful: REVERSED order vs SQL Server, no unit)
SELECT DATEDIFF(delivery_date, order_date) FROM orders;

-- Oracle (date - date = days, possibly fractional)
SELECT delivery_date - order_date FROM orders;

The classic trap: DATEDIFF exists in both engines with reversed argument order. MySQL wants the later date first.

6. Formatting a date as text

-- PostgreSQL / Oracle
SELECT TO_CHAR(order_date, 'YYYY-MM') FROM orders;

-- SQL Server
SELECT FORMAT(order_date, 'yyyy-MM') FROM orders;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m') FROM orders;

The standard grouping key for monthly GROUP BY reports: the year-month string.

One closing note: when combining these with date range filters, remember the half-open interval rule (start inclusive, end exclusive); details in our date filtering guide. Time zones are their own trouble: if the column stores UTC, midnight records land on the wrong day unless you compute "today" in the same zone.

Frequently asked questions

What is the difference between GETDATE and SYSDATETIME?: Both return the current time in SQL Server; GETDATE returns datetime (3 ms precision), SYSDATETIME returns datetime2 (100 ns). Prefer SYSDATETIME in new code, though either works for reporting.
Why does MySQL have no date_trunc?: MySQL never added it; the community uses the DATE_FORMAT first-day pattern. Recent MariaDB versions have similar functions, but stick to the pattern if you want portability.
How do I write a date literal in Oracle?: The safest is the ANSI form: DATE '2026-01-01'. TO_DATE('01/01/2026','DD/MM/YYYY') works too, but avoid formats that depend on session settings.
Do I have to memorize these differences?: You do not; that is exactly why this page exists. One step further is not thinking about the dialect at all: type your question into PerSight in plain language, and it picks the date functions matching whichever engine you are connected to, showing you the SQL it generated.