SQL Date and Time Functions

This section provides detailed reference information on the built-in date and time functions available in SQL Server.

Introduction

SQL Server offers a rich set of functions to manipulate and retrieve date and time information. These functions are crucial for tasks such as logging events, scheduling, calculating durations, and filtering data based on temporal criteria.

Commonly Used Functions

GETDATE()

Returns the current database system date and time as a datetime2(3) value. This is the recommended function for retrieving the current timestamp.

SELECT GETDATE();

SYSDATETIME()

Returns the date and time of the server on which SQL Server is running. It is a datetime2(7) data type.

SELECT SYSDATETIME();

DATEPART(datepart, date)

Returns an integer representing the specified part of a specified date. datepart can be year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, etc.

SELECT DATEPART(year, '2023-10-27'); -- Returns 2023
SELECT DATEPART(month, GETDATE()); -- Returns the current month number

DATENAME(datepart, date)

Returns a character string that represents the specified part of a specified date. The return value alternates between the name of the date part and the full name of the date part, depending on the locale setting of the server.

SELECT DATENAME(weekday, '2023-10-27'); -- Returns 'Friday'
SELECT DATENAME(month, GETDATE()); -- Returns the name of the current month

DATEADD(datepart, number, date)

Returns a new datetime value to which is added a specified time interval (in units of that datepart) to a specified date.

SELECT DATEADD(day, 7, '2023-10-27'); -- Returns '2023-11-03'
SELECT DATEADD(month, -3, GETDATE()); -- Returns the date 3 months ago

DATEDIFF(datepart, startdate, enddate)

Returns the difference between two specified dates. The return value is a signed integer that represents the number of date parts of the type datepart crossed between startdate and enddate.

SELECT DATEDIFF(day, '2023-10-20', '2023-10-27'); -- Returns 7
SELECT DATEDIFF(month, '2023-01-15', '2023-10-27'); -- Returns 9

FORMAT(date, format [, culture])

Formats a value and returns it as a string. This function is available from SQL Server 2012 and later.

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss'); -- e.g., '2023-10-27 14:30:00'
SELECT FORMAT(GETDATE(), 'dd MMMM yyyy'); -- e.g., '27 October 2023'
SELECT FORMAT(GETDATE(), 'd', 'en-US'); -- e.g., '10/27/2023'

Date and Time Data Types

Understanding the different date and time data types is crucial for effective use of these functions:

• DATE: Stores date only.
• TIME: Stores time only.
• DATETIME: Stores date and time.
• DATETIME2: More modern and precise than DATETIME, with adjustable precision.
• SMALLDATETIME: Stores date and time, but with less precision and range than DATETIME.
• DATETIMEOFFSET: Stores date and time with a time zone offset.

Best Practices

• Use GETDATE() or SYSDATETIME() for current timestamps.
• Be mindful of data types when performing date arithmetic to avoid unexpected results.
• Use FORMAT() for complex, culture-aware formatting, but consider CONVERT() for simpler, performance-critical scenarios.
• Always specify the desired datepart explicitly in functions like DATEPART, DATEADD, and DATEDIFF.

For a comprehensive list and detailed syntax of each function, please refer to the official SQL Server documentation.