SQL Get Current Date

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: