Date and Time Scalar Functions
These functions return a value of a date or time data type. They are essential for manipulating and querying date and time information within SQL Server.
Category: Date and Time Functions
GETDATE()
Returns the current database system timestamp as a datetime2 value.
SELECT GETDATE() AS CurrentDateTime;DATEPART(datepart, date)
Returns an integer representing the specified part of the specified date. For example, returns the year or month.
SELECT DATEPART(year, '2023-10-27') AS YearPart;DATENAME(datepart, date)
Returns a character string representing the specified part of the specified date. For example, returns the name of the month.
SELECT DATENAME(month, '2023-10-27') AS MonthName;DATEDIFF(datepart, startdate, enddate)
Returns the difference between two dates, in the specified datepart.
SELECT DATEDIFF(day, '2023-10-01', '2023-10-27') AS DaysDifference;DATEADD(datepart, number, date)
Returns a new datetime value by adding or subtracting a specified number of intervals to a specified date.
SELECT DATEADD(month, 3, '2023-10-27') AS FutureDate;DAY(date)
Returns the day of the month as an integer.
SELECT DAY('2023-10-27') AS DayOfMonth;MONTH(date)
Returns the month as an integer.
SELECT MONTH('2023-10-27') AS MonthOfYear;YEAR(date)
Returns the year as an integer.
SELECT YEAR('2023-10-27') AS YearValue;FORMAT(value, format [, culture])
Formats a value and converts it to a string. Widely used for date and time formatting.
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss', 'en-US') AS FormattedDateTime;SYSDATETIME()
Returns the date and time of the server on which the instance of SQL Server is running. This is a datetime2 value.
SELECT SYSDATETIME() AS SystemDateTime;