FB

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.

 

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.

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.