How to Get the Current Date and Time
in SQL Server

SQL Server functions for current date and time

SQL Server provides several functions aimed to help users extract, convert, and format the date and time values with details, including those focused on processing dates or times separately, and those performing operations on the full date value that includes the time. In this section, we review these functions and their work. To illustrate our cases, we use dbForge Studio for SQL Server, a comprehensive integrated development environment for SQL Server databases.

GETDATE()

The simplest and most common way to retrieve the current date value in SQL Server is the built-in function GETDATE(). This function brings the datetime value that contains both the date and time of the computer where the SQL Server instance runs. The output format is yyyy-mm-dd hh:mm:ss.

The basic syntax of the query is as follows:

SELECT GETDATE()

GETDATE() is a T-SQL-specific function. Therefore, it may not be present in other SQL dialects or have a different implementation.

SYSDATETIME()

Another way to get the current date and time in SQL Server is with the SYSDATETIME() function. It returns the current system date and time as a datetime2(7) value from the computer where the SQL Server instance is installed and run. The time zone offset is not included in the returned value.

The basic syntax of the query is as follows:

SELECT SYSDATETIME()

The SYSDATETIME() results have higher precision than the results of GETDATE(). Therefore, for more accurate calculations, using SYSDATETIME() can be more relevant.

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function is an ANSI SQL equivalent to GETDATE(), and it is used in the same use cases. It returns the value in a datetime format derived from the operating system of the computer with the SQL Server instance. The value format is yyyy-mm-dd hh:mm:ss, the same as with GETDATE().

The basic syntax of the query is as follows:

SELECT CURRENT_TIMESTAMP

Comparison of GETDATE(), SYSDATETIME() and CURRENT_TIMESTAMP

The choice of the function to retrieve the current date and time in SQL Server depends on your specific conditions. Here we have compiled a comparison table for all three functions:

Extracting specific parts of the date and time value

In many scenarios, it becomes necessary to extract specific portions of a date and time value. For example, you might need only the date, the time, or individual components such as the day, month, or hour. SQL Server offers built-in functions to efficiently handle these tasks.

Using CAST() and CONVERT()

The CAST() and CONVERT() functions used with the GETDATE() function allow you to retrieve either the date or time values separately.

Getting the current date without time

When applied to a datetime value, the CAST() or CONVERT() function extracts the current date in the yyyy-mm-dd format, omitting the time.

SELECT
  CAST(GETDATE() AS DATE) AS 'DateOnly'

Or

SELECT
  CONVERT(DATE, GETDATE()) AS 'DateOnly'

The GETDATE() function retrieves the current date and time. The CAST() or CONVERT() function converts DATETIME into DATE and ensures extracting and returning only the date portion of the current timestamp.

Getting the current time without the date

The CAST() or CONVERT() functions can be used to retrieve only the time portion, excluding the date, in the hh:mm:ss format. The following example illustrates how to extract the time value:

SELECT
  CAST(GETDATE() AS TIME) AS 'TimeOnly'

Or

SELECT
  CONVERT(TIME, GETDATE()) AS 'TimeOnly'

Using DATEPART() and FORMAT()

SQL Server provides the ability to extract specific components from a timestamp value, such as the year, month, day, hour, minute, etc. so that we can work with and manipulate these individual elements. The DATEPART() function is designed specifically for this purpose.

The syntax is:

SELECT DATEPART(interval, date)

Parameters:

• interval - the timestamp component to return. The supported intervals are: year (yyyy, yy), quarter (qq, q), month (mm, m), day (dd, d), week (ww, wk), weekday (dw, w), hour (hh), minute (min), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns).
• date - the particular date to use. It can be date, datetime, datetimeoffset, datetime2, smalldatetime, or time data types.

The following query retrieves separate components from any specific timestamp (we use the 2024-12-13 04:16:18.060 datetime value):

SELECT
  DATEPART(year, '2024-12-13 04:16:18.060') AS 'Year'
 ,DATEPART(month, '2024-12-13 04:16:18.060') AS 'Month'
 ,DATEPART(day, '2024-12-13 04:16:18.060') AS 'Day'
 ,DATEPART(dayofyear, '2024-12-13 04:16:18.060') AS 'DayOfYear'
 ,DATEPART(weekday, '2024-12-13 04:16:18.060') AS 'WeekDay'
 ,DATEPART(hour, '2024-12-13 04:16:18.060') AS 'Hour'
 ,DATEPART(minute, '2024-12-13 04:16:18.060') AS 'Minute'
 ,DATEPART(second, '2024-12-13 04:16:18.060') AS 'Second'

We can also combine DATEPART() with GETDATE() to extract the timestamp components from the actual date:

SELECT
  DATEPART(year, GETDATE()) AS 'Year'
 ,DATEPART(month, GETDATE()) AS 'Month'
 ,DATEPART(day, GETDATE()) AS 'Day'
 ,DATEPART(dayofyear, GETDATE()) AS 'DayofYear'
 ,DATEPART(weekday, GETDATE()) AS 'WeekDay'

The DATEPART() function in SQL Server is helpful in many common scenarios, such as date-based filtering and grouping, calculating time differences, formatting date values, etc.

Handling time zones and UTC in SQL Server

Standard SQL Server functions like GETDATE() and SYSDATETIME() retrieve the current system date and time. However, in certain scenarios, you may need the Coordinated Universal Time (UTC) instead. SQL Server provides two functions: GETUTCDATE() and SYSUTCDATETIME().

GETUTCDATE() returns the current UTC date and time in the format yyyy-mm-dd hh:mm:ss.mmm. The value is based on the operating system of the computer where SQL Server is running and is recalculated to UTC.

The syntax is:

SELECT GETUTCDATE()

SYSUTCDATETIME() returns a datetime2 value in the format yyyy-mm-dd hh:mi:ss. Like GETUTCDATE(), the result is derived from the system settings of the SQL Server host and adjusted to UTC. The main difference is the precision - SYSUTCDATETIME() provides a default precision of up to 7 digits for fractional seconds.

The syntax is:

SELECT SYSUTCDATETIME()

Both functions are useful for scenarios requiring UTC or global timekeeping.

Converting between time zones

By default, the regional database settings are set to UTC. However, there may be cases where you need to query data directly and view the local date and time or calculate the date and time for another region. For such scenarios, SQL Server provides the AT TIME ZONE feature, introduced in SQL Server 2016.

The syntax of the query is:

SELECT
  inputdate AT TIME ZONE name_of_destination_time_zone

To view the list of supported time zones in SQL Server, run:

SELECT
  *
FROM sys.time_zone_info

For instance, we need to convert the UTC time to the Pacific Standard Time:

SELECT
  CAST('2024-12-16T10:00:00' AS DATETIME2)
  AT TIME ZONE 'UTC'
  AT TIME ZONE 'Pacific Standard Time' AS 'PST'

The accepted input data types are SMALLDATETIME, DATETIME, and DATETIME2, and the output is returned as a DATETIMEOFFSET data type.

Formatting the date and time

Working with date and time values is a common task in database management. Often, these values need to be converted into simpler formats for various purposes, such as generating date-based reports where time is not essential, simplifying date comparisons for data analysis, or performing aggregate calculations focused solely on dates.

One of the key tasks is formatting dates correctly. SQL Server provides three primary functions for this purpose: CONVERT(), CAST(), and FORMAT(). These functions allow you to convert date and time expressions into other data types, such as VARCHAR.

With CAST() and CONVERT() functions

Assume we need to convert the datetime value into VARCHAR with the help of the CAST() function. The query is as follows:

SELECT
  CAST('2024-12-17 18:45:00' AS VARCHAR(20)) AS 'ConvertedValue'

The output is in the VARCHAR format:

The CONVERT() function allows you to convert a DATETIME value into a string (VARCHAR) with a specific format. The format is controlled by a style code, a numeric value that defines how the date will appear.

The syntax is:

SELECT
  CONVERT(VARCHAR, datetime_value, style)

The most common style codes are:

If no style is specified, the default format is used, which may vary based on server settings.

We can perform several format conversions with one query:

DECLARE @DateTimeValue DATETIME = '2024-12-17 19:30:00';

SELECT
  CONVERT(VARCHAR, @DateTimeValue, 121) AS ISOFormat
 ,  -- ISO 8601 format
  CONVERT(VARCHAR, @DateTimeValue, 103) AS BritishFormat
 , -- dd/mm/yyyy
  CONVERT(VARCHAR, @DateTimeValue, 101) AS USFormat; -- mm/dd/yyyy

Using the correct style code allows you to adapt the output to regional or application requirements easily.

With the FORMAT() function

The FORMAT() function provides more flexibility than CONVERT() allowing for custom date and time formats. It accepts two arguments:

• The value to format (a DATETIME value)
• The format string, which defines the desired output

SELECT
  FORMAT(datetime_value, 'format_string')

Assume we want to convert the current date and time to custom VARCHAR format - dd-MMM-yyyy, where MMM stands for the abbreviated month name. This format is common in reports.

SELECT
  FORMAT(GETDATE(), 'dd-MMM-yyyy') AS 'FormattedDate'

Or, convert the datetime value to VARCHAR with time in 24-hour format, which is often used in logs and time-sensitive applications:

SELECT
  FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS 'FormattedDate'

While both functions are useful for converting DATETIME values to VARCHAR, choosing between them depends on your specific formatting needs and the SQL Server version. CONVERT() uses predefined style codes for formatting; it is efficient and widely supported but less flexible for custom formats. FORMAT() supports custom formats and is highly versatile but requires SQL Server 2012 or newer.

Date and time arithmetic

Handling time intervals, such as adding or subtracting durations and calculating the difference between dates, is a common task in database management. SQL Server provides specialized functions to manage these operations efficiently.

Add and subtract time intervals with DATEADD()

The DATEADD() function in SQL Server allows you to add or subtract specific time units to or from a given date. This is useful for calculating deadlines, determining end times, finding historical data by subtracting months or years, etc.

The syntax is:

SELECT DATEADD(interval, number, date)

Parameters:

• interval - the date part to add or subtract (e.g., year, month, day, hour, minute)
• number - the value to add (positive) or subtract (negative) to/from the specified date part
• date - the date value to which the calculation applies

Assume we want to add 7 days to the current date. The query is:

SELECT
  DATEADD(DAY, 7, GETDATE()) AS 'NewDate'

To subtract a specific time interval from a date, use a negative number in the query. For example, subtract 10 days from a given date:

SELECT
  DATEADD(DAY, -10, GETDATE()) AS 'NewDate'

Calculate differences between dates with DATEDIFF()

The DATEDIFF() function calculates the difference between two dates or timestamps in a specified unit (e.g., days, months, years).

SELECT DATEDIFF(datepart, startdate, enddate)

Parameters:

• datepart - the time unit to measure the difference (e.g., year, month, day)
• startdate and enddate the two dates or timestamps to compare

To calculate the difference in months between two dates, execute the query

SELECT
  DATEDIFF(MONTH, '2024-12-17', '2022-06-01') AS 'Difference'