How to Get Current Date in SQL Server

By: Jim Evans   |   Updated: 2021-04-09   |   Comments (2)   |   Related: More > Dates

Problem

In this tutorial we will explore the different options for getting the current date from SQL Server and understand when to use one option over the other.

Solution

There are multiple ways to get the current date in SQL Servers using T-SQL and database system functions. In this tutorial I will show the different functions, discuss the differences between them, suggest where to use them as a SQL Reference Guide. I will then present several code usage examples.

Usage Options

SQL Server provides several different functions that return the current date time including: GETDATE(), SYSDATETIME(), and CURRENT_TIMESTAMP. The GETDATE() and CURRENT_TIMESTAMP functions are interchangeable and return a datetime data type.

The SYSDATETIME() function returns a datetime2 data type. Also SQL Server provides functions to return the current date time in Coordinated Universal Time or UTC which include the GETUTCDATE() and SYSUTCDATETIME() system date functions.

SQL Server provides an additional function, SYSDATETIMEOFFSET(), that returns a precise system datetime value with the SQL Server current time zone offset.

You can use SELECT CAST or SELECT CONVERT to change the data type being returned by these functions to Date, smalldatetime, datetime, datetime2, and character data types. Below shows the precision and range for each function.

Here is a comparison of the different options.

Function

Data Type

Precision

Range

GETDATE()

Datetime

19 positions minimum to 23 maximum; Rounded to increments of .000, .003, or .007 seconds

1753-01-01 through 9999-12-31; 00:00:00 through 23:59:59.997

CURRENT_TIMESTAMP

Datetime

19 positions minimum to 23 maximum; Rounded to increments of .000, .003, or .007 seconds

1753-01-01 through 9999-12-31; 00:00:00 through 23:59:59.997

SYSDATETIME()

Datetime2

27 maximum (YYYY-MM-DD hh:mm:ss.0000000); 100 nanoseconds

0001-01-01 through 9999-12-31; 00:00:00 through 23:59:59.9999999

GETUTCDATE()

Datetime

19 positions minimum to 23 maximum; Rounded to increments of .000, .003, or .007 seconds

1753-01-01 through 9999-12-31; 00:00:00 through 23:59:59.997

SYSUTCDATETIME()

Datetime2

27 maximum (YYYY-MM-DD hh:mm:ss.0000000); 100 nanoseconds

0001-01-01 through 9999-12-31; 00:00:00 through 23:59:59.9999999

SYSDATETIMEOFFSET()

Datetimeoffset(n). n is the fractional seconds precision and can range from 0 to 7. hh:mm:ss[.nnnnnnn]

YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm;

Same as SYSDATETIME(): 2007-05-08 12:35:29.1234567 +12:15; hh range from 00 to 14 and mm ranging from 00 to 59 and -14:00 through +14:00

Why use one over the other?

The GETDATE() function is the most commonly used of the set of functions.

The CURRENT_TIMESTAMP function can be used anywhere that the GETDATE() function is used. It is the ANSI equivalent to the GETDATE() function. Both return the exact same results and are of the datetime data type.

The SYSDATETIME() function is rarely used and is of datatime2 data type which is more precise in fractions of a second. This would be used if higher precision is required.

If Coordinated Universal Time or UTC is required, either GETUTCDATE() or SYSUTCDATETIME() can be used, the latter is of higher precision if needed.

Last, to get the current date time in datetime2 precision and show the current time zone off set, use SYSDATETIMEOFFSET(). This function returns the results as data type Datetimeoffset(7).

Usage Example

Example SELECT statement calling each current date function.

-- Current Datetime
SELECT 
   GETDATE()         as 'GETDATE()'  
  ,CURRENT_TIMESTAMP as 'CURRENT_TIMESTAMP' 
  ,SYSDATETIME()     as 'SYSDATETIME()'; 

--UTC and Time zone Offset
SELECT  
   SYSDATETIMEOFFSET() as 'SYSDATETIMEOFFSET()'  
  ,GETUTCDATE()        as 'GETUTCDATE()' 
  ,SYSUTCDATETIME()    as 'SYSUTCDATETIME()';  
GO 

Results: The first result shows the current local time of the server and the different precisions. The second result shows the current time with the time zone offset and UTC date times with different precisions. The actual results will vary based on your server time and time zone.

Solution – SQL Examples

Next, I will show many different usage examples of the current datetime functions. For some of the examples I will use Microsoft’s sample AdventureWorks database.

Example 1 – Use as a Parameter for a Script

In this example I will declare variables with different data types and set their values using different current datetime functions and then show their results.

DECLARE @MySmallDateTime smalldatetime = CURRENT_TIMESTAMP;
DECLARE @MyDateTime datetime = GETDATE();
DECLARE @MyDateTime2 datetime2 = SYSDATETIME();
DECLARE @MyDateTimeoffSet datetimeoffset(7) = SYSDATETIMEOFFSET();
 
SELECT @MySmallDateTime as 'SmallDateTime',
   @MyDateTime as 'DateTime',
   @MyDateTime2 as 'DateTime2',
   @MyDateTimeoffSet as 'SYSDATETIMEOFFSET(0)'
GO

Results: The scripting example showing results with different data types. For SYSDATETIMEOFFSET(), I specified 0 as the fractional seconds precision. Note that SYSDATETIMEOFFSET(0) includes my server local time zone offset.

Example 2 – Use in a WHERE clause

This example will show the usage of the current datetime functions in Where clauses.

--Note Each of these return the same results
SELECT * FROM [Purchasing].[PurchaseOrderDetail] WHERE ModifiedDate < GETDATE();
SELECT * FROM [Purchasing].[PurchaseOrderDetail] WHERE ModifiedDate < CURRENT_TIMESTAMP;
SELECT * FROM [Purchasing].[PurchaseOrderDetail] WHERE ModifiedDate < SYSDATETIME();
GO

Results:Below shows one of a partial result set from the Where clause example. All 3 results in this case are the same.

Example 3 – Use in a CASE statement

This example shows use of CURRENT_TIMESTAMP in a CASE statement.

DECLARE @MyDateTime smalldatetime = CURRENT_TIMESTAMP  
 
SELECT CASE 
   WHEN @MyDateTime > CAST(CURRENT_TIMESTAMP as smalldatetime) THEN 'Your are early'
   WHEN @MyDateTime = CAST(CURRENT_TIMESTAMP as smalldatetime) THEN 'Your are on time'
   WHEN @MyDateTime < CAST(CURRENT_TIMESTAMP as smalldatetime) THEN 'Your are late'
 END as 'Results'
GO

Results: In the CASE statement results the converted smalldatetime date value rolls to the nearest minute showing 00 seconds.

Example 4 – Use as Default in a Table Schema

In this example I will create a table with 3 date data type columns and will define each with a default value of each of the current date functions. I will insert 3 rows to the table and select the results.

Create Table #MSTips_Currentdate (
   id int identity(1,1),
   Modified_by nvarchar(20),
   Modified_On smalldatetime default GETDATE(),
   Created_On datetime default CURRENT_TIMESTAMP,
   Sys_Change_Date datetime2 default SYSDATETIME()
);
GO

insert into #MSTips_Currentdate (Modified_by) values ('MSSQLTips-1');
insert into #MSTips_Currentdate (Modified_by) values ('MSSQLTips-2');
insert into #MSTips_Currentdate (Modified_by) values ('MSSQLTips-3');
select * from #MSTips_Currentdate;
 
Drop table #MSTips_Currentdate;
GO

Results: Table Default results showing the default current date columns being automatically populated. Also, here you can see the smalldatetime rounding to the nearest minute.

Example 5 – Return Just the Date

This example declares variables with data type DATE and sets the values using different Current Date Time functions.

DECLARE @MySmallDateTime DATE = CURRENT_TIMESTAMP;
DECLARE @MyDateTime DATE = GETDATE();
DECLARE @MyDateTime2 DATE = SYSDATETIME();
DECLARE @MyDateTimeoffSet DATE = SYSDATETIMEOFFSET();
 
SELECT @MySmallDateTime as 'SmallDateTime',
   @MyDateTime as 'DateTime',
   @MyDateTime2 as 'DateTime2',
   @MyDateTimeoffSet as 'SYSDATETIMEOFFSET(0)' 
GO

Results: Set current date time function to s DATE variable results.

Example 5a – Use CAST to Return Just DATE

Another technique is to use CAST to return just the Date.

SELECT CAST(CURRENT_TIMESTAMP as DATE);
SELECT CAST(GETDATE() as DATE);
SELECT CAST(SYSDATETIME() as DATE);
SELECT CAST(SYSDATETIMEOFFSET() as DATE);
GO

Results: Cast to DATE results.

It is also possible to use SELECT CONVERT rather than CAST.

Example 6 – Use as a parameter for a stored procedure

When calling a stored procedure that takes a date parameter, unfortunately you cannot pass the current date functions directly. You can declare a variable of data type datetime and use the variable passed to the stored procedure parameter.

Example 6a – Create a Test Stored Procedure

First, we create a test stored procedure.

CREATE PROC My_DateTime_Proc
   @MyDateTime DATETIME
as
SELECT @MyDateTime as 'Current_DateTime';
GO

Example 6b – Attempt to pass the Function Directly

Next, we will attempt a SP call passing current timestamp directly to see the error raised.

--Incorrect syntax near the keyword 'CURRENT_TIMESTAMP'.
exec My_DateTime_Proc @MyDateTime = CURRENT_TIMESTAMP;
GO
 
--Incorrect syntax near ')'.
exec My_DateTime_Proc @MyDateTime = GETDATE();
GO

Results: Both Stored Procedure calls error out. See both error below.

Example 6c – Proper way to pass Current Date to a Stored Procedure

Now, I will show declaring a variable, setting the value to current timestamp and calling the SP passing the current timestamp variable.

Declare @Input datetime = CURRENT_TIMESTAMP
exec My_DateTime_Proc @MyDateTime = @Input;
GO

Results: Stored Procedure successful results.

Wrap Up

There are several ways to get Current Date and Time values in MSSQL with numerous SQL functions. Hopefully, this tutorial helped to identify the different options and highlighted differences between them and will aid in identifying when to use each one.

Next Steps

About the author