How to Get Current Date in SQL Server


By:   |   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.

result set

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.

result set

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.

result set

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.

result set

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.

result set

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.

result set

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.

result set

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.

error message

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.

result set

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


Last Updated: 2021-04-09


get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently with Harsco who has managed DBAs, Application and BI Developers and Data Management teams for over 20 years.

View all my tips



Comments For This Article




Friday, April 09, 2021 - 7:49:11 PM - Piquet Back To Top (88507)
Aside from precision, one other reason for choosing datetime2 over datetime is to support a wider range of date times. i.e. date times prior to 1753

Friday, April 09, 2021 - 3:50:30 AM - Francesco Mantovani Back To Top (88502)
You can give up your SYSDATETIMEOFFSET() higher precision when you move to Azure SQL Database: https://dba.stackexchange.com/questions/287688/why-is-sysdatetimeoffset-precision-decreased-on-azure-sql


download





Recommended Reading

Date and Time Conversions Using SQL Server

Add and Subtract Dates using DATEADD in SQL Server

Format SQL Server Dates with FORMAT Function

Creating a date dimension or calendar table in SQL Server

SQL Convert Date to YYYYMMDD














get free sql tips
agree to terms