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.

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.

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.

1SELECT
2SYSDATETIME(),
3SYSDATETIMEOFFSET(),
4SYSUTCDATETIME(),
5CURRENT_TIMESTAMP,
6GETDATE(),
7GETUTCDATE();

The results are below (translated to rows to make it easier to compare):

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:

1SELECT
2CAST(GETDATE() AS DATE) AS date_cast,
3CONVERT (date, GETDATE()) AS date_convert;

Result:

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.

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.

1SELECT
2NOW(),
3SYSDATE(),
4CURDATE(),
5CURRENT_DATE(),
6UTC_DATE(),
7UTC_TIMESTAMP();

I've included CURRENT_DATE as it's commonly used.

The results are below (translated to rows to make it easier to compare):

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:

1SELECT NOW(), SLEEP(5), NOW();

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

1SELECT SYSDATE(), SLEEP(5), SYSDATE();

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.

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.

1SELECT
2SYSDATE,
3CURRENT_DATE,
4CURRENT_TIMESTAMP,
5LOCALTIMESTAMP,
6SYSTIMESTAMP
7FROM dual;

The results are below (translated to rows to make it easier to compare):

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:

1SELECT
2SYSDATE,
3TO_CHAR(SYSDATE, 'YYYY-MM-DD hh:mi:ss') AS sysdate_full,
4CURRENT_DATE,
5TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD hh:mi:ss') AS current_date_full
6FROM dual;

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:

The return values of these functions look like this:

1SELECT
2CURRENT_DATE,
3CURRENT_TIMESTAMP,
4LOCALTIMESTAMP,
5NOW();

Results:

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: