The CURRENT_TIMESTAMP function returns the current date and time as a datetime value. This value is derived from the operating system of the computer that the instance of SQL Server is running on.

This function is the ANSI SQL equivalent to the T-SQL GETDATE() function, so you can use whichever one you prefer. Note that both functions have a lower date range and a lower default fractional precision than the T-SQL SYSDATETIME() function (which returns a datetime2(7) value).

This article provides examples of the CURRENT_TIMESTAMP function, including how you can use it with other functions to return the value you’re interested in.

Syntax

The syntax goes like this:

CURRENT_TIMESTAMP

So you simply call this function without any arguments.

Example

Here’s a basic example of using a SELECT statement to return the current date and time from CURRENT_TIMESTAMP:

SELECT CURRENT_TIMESTAMP AS Result;

Result:

+-------------------------+
| Result                  |
|-------------------------|
| 2018-06-16 00:06:36.740 |
+-------------------------+

Extract a Part of the Date

If you only want a part of the return value, you can use DATEPART() to return only that part of the date/time that you’re interested in.

Example:

SELECT DATEPART(month, CURRENT_TIMESTAMP) AS Result;

Result:

+----------+
| Result   |
|----------|
| 6        |
+----------+

Here’s another example using the MONTH() function. The result is the same.

SELECT MONTH(CURRENT_TIMESTAMP) AS Result;

Result:

+----------+
| Result   |
|----------|
| 6        |
+----------+

Both of those functions return the current month. But they return them as an integer representing the month number.

If you want the month name returned instead, you can use DATENAME():

SELECT DATENAME(month, CURRENT_TIMESTAMP) AS Result;

Result:

+----------+
| Result   |
|----------|
| June     |
+----------+

 

Format the Date

You can also use other T-SQL functions to format the date as required.

Here’s an example of using the FORMAT() function to format the result:

SELECT 
    FORMAT(CURRENT_TIMESTAMP, 'd', 'en-US') AS 'd, en-US',
    FORMAT(CURRENT_TIMESTAMP, 'd', 'en-gb') AS 'd, en-gb',
    FORMAT(CURRENT_TIMESTAMP, 'D', 'en-US') AS 'D, en-US',
    FORMAT(CURRENT_TIMESTAMP, 'D', 'en-gb') AS 'D, en-gb';

Result:

+------------+------------+-------------------------+--------------+
| d, en-US   | d, en-gb   | D, en-US                | D, en-gb     |
|------------+------------+-------------------------+--------------|
| 6/16/2018  | 16/06/2018 | Saturday, June 16, 2018 | 16 June 2018 |
+------------+------------+-------------------------+--------------+

More examples at How to Format the Date and Time in SQL Server.

Incrementing the Value and Finding the Difference

You can use functions like DATEDIFF() to return the difference between the current date and another date.

Here’s an example of using DATEADD() to add a month to the current date, then finding out the difference in days:

DECLARE @date1 datetime2 = CURRENT_TIMESTAMP;
DECLARE @date2 datetime2 = DATEADD(month, 1, CURRENT_TIMESTAMP);
SELECT DATEDIFF(day, @date1, @date2) AS Result;

Result:

+----------+
| Result   |
|----------|
| 30       |
+----------+