Do you need to find the current date using SQL?
There are a few ways to do this in the various database vendors.
Let’s take a look at them.
Summary
Here’s a summary of how to get the current date in each database.
Vendor | Method |
SQL Server | GETDATE() or CURRENT_TIMESTAMP |
MySQL | SYSDATE() or NOW() |
Oracle | SYSDATE() or CURRENT_DATE |
PostgreSQL | CURRENT_DATE or CURRENT_TIMESTAMP |
Read on to find out the syntax and example output of each of them.
Current Date in SQL
To get the current date in SQL, you run either GETDATE (SQL Server), SYSDATE (MySQL or Oracle), or CURRENT_DATE (PostgreSQL).
SQL Server
There are several ways to get the current date in SQL Server, and all of them use functions.
Function | Data Type | Returns |
SYSDATETIME() | datetime2 | Server date time to 7 fractional seconds |
SYSDATETIMEOFFSET() | datetimeoffset | Server date time with time zone to 7 fractional seconds |
SYSUTCDATETIME() | datetime2 | Server date time in UTC to 7 fractional seconds |
CURRENT_TIMESTAMP | datetime | Server date time to 3 fractional seconds |
GETDATE() | datetime | Server date time to 3 fractional seconds |
GETUTCDATE() | datetime | Server date time in UTC to 3 fractional seconds |
These six functions all return the date and time to different amounts of fractional seconds or based on time zones. Notice that CURRENT_TIMESTAMP does not need any opening or closing brackets.
(You can read more about MySQL date data types and functions in this guide)
Let’s see an example of this.
SELECT
SYSDATETIME(),
SYSDATETIMEOFFSET(),
SYSUTCDATETIME(),
CURRENT_TIMESTAMP,
GETDATE(),
GETUTCDATE();
The results are below (translated to rows to make it easier to compare):
Function | Result |
SYSDATETIME() | 2020-07-15 16:24:38.7750094 |
SYSDATETIMEOFFSET() | 2020-07-15 16:24:38.7750094 +00:00 |
SYSUTCDATETIME() | 2020-07-15 06:24:38.7750094 |
CURRENT_TIMESTAMP | 2020-07-15 16:24:38.773 |
GETDATE() | 2020-07-15 16:24:38.773 |
GETUTCDATE() | 2020-07-15 06:24:38.773 |
You can see some differences here:
- The first three functions show fractional seconds to 7 places.
- The two UTC time functions show the current time in the UTC time zone. I ran these functions in Melbourne, Australia, which is UTC +10.
You can also see that CURRENT_TIMESTAMP and GETDATE() show the same values.
CURRENT_TIMESTAMP vs GETDATE
What’s the difference between CURRENT_TIMESTAMP and GETDATE() in SQL Server?
CURRENT_TIMESTAMP is an ANSI SQL compliant function, meaning it complies with the standards (and is likely to exist in other database vendors). GETDATE() is the T-SQL version of that function.
How to Get Just the Current Date in SQL Server
The functions above show the current date and time.
But what if you only want to get the current date, and not the time?
There are two ways you can do that: use CAST or use CONVERT.
I’ve written about this method in a recent post: How to Convert DATETIME to DATE in SQL Server.
Here’s an example:
SELECT
CAST(GETDATE() AS DATE) AS date_cast,
CONVERT (date, GETDATE()) AS date_convert;
Result:
date_cast | date_convert |
2020-07-15 | 2020-07-15 |
Both methods return the same value.
While you’re here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here:
MySQL
There are a few ways to get the current date and time in MySQL.
Function | Returns |
NOW() | Current date and time in session time zone in YYYY-MM-DD hh:mm:ss |
SYSDATE() | Current date and time in session time zone in YYYY-MM-DD hh:mm:ss |
CURRENT_DATE() | Synonym for CURDATE |
CURDATE() | Current date in YYYY-MM-DD |
CURRENT_TIMESTAMP() | Synonym for NOW |
LOCALTIME() | Synonym for NOW |
LOCALTIMESTAMP() | Synonym for NOW |
UTC_DATE() | Current date and time in UTC time zone in YYYY-MM-DD |
UTC_TIMESTAMP() | Current date and time in UTC time zone in YYYY-MM-DD hh:mi:ss |
Where a function is a synonym for another, it means it just has another name. For example, calling CURRENT_TIMESTAMP is the same as calling NOW.
So, excluding those functions that are synonyms, there are only a few functions:
- NOW
- SYSDATE
- CURDATE
- UTC_DATE
- UTC_TIMESTAMP
Let’s see an example of them.
SELECT
NOW(),
SYSDATE(),
CURDATE(),
CURRENT_DATE(),
UTC_DATE(),
UTC_TIMESTAMP();
I’ve included CURRENT_DATE as it’s commonly used.
The results are below (translated to rows to make it easier to compare):
Function | Result |
NOW | 2020-07-15 16:55:47 |
SYSDATE | 2020-07-15 16:55:47 |
CURDATE | 2020-07-15 |
CURRENT_DATE | 2020-07-15 |
UTC_DATE | 2020-07-15 |
UTC_TIMESTAMP | 2020-07-15 06:55:47 |
You can see a few things here:
- NOW and SYSDATE show the same value.
- CURDATE and CURRENT_DATE are the same, as expected, as one is a synonym of the other.
- UTC_DATE is the same as the other two functions as it just happens to be the same day in UTC time.
- UTC_TIMESTAMP shows the current time in UTC, which is 10 hours behind my current time.
What’s the Difference Between NOW, SYSDATE and CURRENT_DATE in MySQL?
All three of these functions (NOW, SYSDATE, and CURRENT_DATE) seem like the same thing.
But they have their differences. How are they different?
CURRENT_DATE (and CURDATE) show just a date value, and don’t include time.
NOW and SYSDATE are very similar. The difference is when the time is calculated:
- NOW returns the time at which the function or triggering statement began to execute
- SYSDATE returns the time at which it actually ran
An example of this can be seen by using the SLEEP function to wait for a few seconds:
SELECT NOW(), SLEEP(5), NOW();
NOW() | SLEEP(5) | NOW() |
2020-07-15 17:00:35 | 0 | 2020-07-15 17:00:35 |
Notice that both calls to NOW show the same result, because that was the time when the SELECT statement started.
We can do the same thing with the SYSDATE function
SELECT SYSDATE(), SLEEP(5), SYSDATE();
SYSDATE() | SLEEP(5) | SYSDATE() |
2020-07-15 17:01:15 | 0 | 2020-07-15 17:01:20 |
Notice that both calls to SYSDATE show a different result, because it shows the time when the actual SYSDATE function was called.
Oracle
There are a few ways to get the current date in Oracle SQL.
Function | Data Type | Returns |
SYSDATE | date | Current date and time of the server |
CURRENT_DATE | date | Current date and time in the session time zone |
CURRENT_TIMESTAMP | timestamp with time zone | Current date and time with time zone in the session time zone |
LOCALTIMESTAMP | timestamp | Current date and time in the session time zone |
SYSTIMESTAMP | timestamp with time zone | Current date and time of the server |
The method you use depends on which data type you want and whether you want the server or session time zone.
Let’s see some examples of these functions.
SELECT
SYSDATE,
CURRENT_DATE,
CURRENT_TIMESTAMP,
LOCALTIMESTAMP,
SYSTIMESTAMP
FROM dual;
The results are below (translated to rows to make it easier to compare):
Function | Result |
SYSDATE | 15-JUL-20 |
CURRENT_DATE | 15-JUL-20 |
CURRENT_TIMESTAMP | 15-JUL-20 12.08.25.095819 AM US/PACIFIC |
LOCALTIMESTAMP | 15-JUL-20 12.08.25.095819 AM |
SYSTIMESTAMP | 15-JUL-20 07.08.25.095818 AM +00:00 |
We can see a few things here:
- SYSDATE and CURRENT_DATE show a similar output.
- CURRENT_TIMESTAMP and LOCALTIMESTAMP show the same value but one is with the timezone and one is without.
One thing to be aware of is that even though SYSDATE and CURRENT_DATE display the date here, they actually include the time.
To see the time component, we can use TO_CHAR, which shows the difference between the two functions:
SELECT
SYSDATE,
TO_CHAR(SYSDATE, 'YYYY-MM-DD hh:mi:ss') AS sysdate_full,
CURRENT_DATE,
TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD hh:mi:ss') AS current_date_full
FROM dual;
Operation | Result |
SYSDATE | 15-JUL-20 |
SYSDATE_FULL | 2020-07-15 06:51:58 |
CURRENT_DATE | 15-JUL-20 |
CURRENT_DATE_FULL | 2020-07-15 11:51:58 |
We can see that both dates are the same, but the times are different.
What’s the Difference Between SYSDATE and CURRENT_DATE in Oracle?
These two functions look like they show the same thing. So how are they different?
SYSDATE is based on the server, and CURRENT_DATE is based on the user’s session.
This means if the user is based in New York and the database server is in London, the SYSDATE function will return the London date and time, and the CURRENT_DATE function will return the New York date and time.
PostgreSQL
To get the current date in PostgreSQL, we can use one of several functions:
Function | Returns |
CURRENT_DATE | Current date and time |
CURRENT_TIMESTAMP | Current date and time with timezone |
LOCALTIMESTAMP | Current date and time |
NOW | Current date and time, same as CURRENT_TIMESTAMP |
The return values of these functions look like this:
SELECT
CURRENT_DATE,
CURRENT_TIMESTAMP,
LOCALTIMESTAMP,
NOW();
Results:
Function | Result |
CURRENT_DATE | 2020-07-16 |
CURRENT_TIMESTAMP | 2020-07-16 05:19:32.201578 EDT |
LOCALTIMESTAMP | 2020-07-16 05:19:32 |
NOW | 2020-07-16 05:19:32.201578 EDT |
We can see that CURRENT_DATE shows only the date, but CURRENT_TIMESTAMP and LOCALTIMESTAMP show the date and time.
What’s the Difference Between CURRENT_TIMESTAMP and LOCALTIMESTAMP in PostgreSQL?
The difference is that LOCALTIMESTAMP returns a timestamp without a timezone, and CURRENT_TIMESTAMP returns a timestamp with a timezone.
Conclusion
There are many ways to get the current date and time in each database. Some of them are common across many databases, such as NOW and CURRENT_DATE, and others are not.
Different functions return different time components, such as fractional seconds and timezone, so be aware of which part of the date and time you need.
While you’re here, if you want an easy-to-use list of the main features in SQL for different vendors, get my SQL Cheat Sheets here:
Don’t forget to mention the major difference with what values are returned in PostgreSQL compared to Oracle. With Oracle, SYSDATE / SYSTIMESTAMP returns the current server datetime/timestamp with every execution. In major contrast, the now() and current_timestamp functions in PostgreSQL return the start time of the current TRANSACTION.
Thus if you start a transaction and insert 1000 rows, each with the now() or current_timestamp function for a timestamp column, all 1000 rows will have exactly the same timestamp value.
To achieve Oracle-like functionality within a PostgreSQL transaction, use the clock_timestamp function.
The excerpt below illustrates the topic, with the SQL run 3 times a few seconds apart. Note how the values for now and current_timestamp do not change at all, only the value for clock_timestamp changes.
postgres=# begin;
BEGIN
postgres=*# select now(), current_timestamp, clock_timestamp();
now | current_timestamp | clock_timestamp
——————————+——————————+——————————-
2023-02-11 18:18:18.12676+00 | 2023-02-11 18:18:18.12676+00 | 2023-02-11 18:18:20.522578+00
(1 row)
postgres=*# select now(), current_timestamp, clock_timestamp();
now | current_timestamp | clock_timestamp
——————————+——————————+——————————-
2023-02-11 18:18:18.12676+00 | 2023-02-11 18:18:18.12676+00 | 2023-02-11 18:18:24.662692+00
(1 row)
postgres=*# select now(), current_timestamp, clock_timestamp();
now | current_timestamp | clock_timestamp
——————————+——————————+——————————-
2023-02-11 18:18:18.12676+00 | 2023-02-11 18:18:18.12676+00 | 2023-02-11 18:18:30.406801+00
Ah thanks for pointing that out Terry, that’s good to know!