SQL Server provides a rich set of date and time functions for handling various operations with date and time values. Here are some of the top date and time functions:
1. GETDATE()
- Purpose: Returns the current date and time of the SQL Server.
- Example:
SELECT GETDATE() AS CurrentDateTime;
- Use Case: Obtaining the current system timestamp for logging or time-stamping records.
2. DATEADD()
- Purpose: Adds a specified number of units (such as days, months, or years) to a date.
- Example:
SELECT DATEADD(DAY, 10, '2024-08-01') AS NewDate;
- Output:
2024-08-11
- Use Case: Calculating future or past dates by adding or subtracting time intervals.
3. DATEDIFF()
- Purpose: Returns the difference between two dates in the specified unit (e.g., days, months, years).
- Example:
SELECT DATEDIFF(DAY, '2024-08-01', '2024-08-18') AS DaysDifference;
- Output:
17
- Use Case: Calculating the difference between two dates, such as the number of days between orders.
4. FORMAT()
- Purpose: Formats a date/time value according to the specified format and culture.
- Example:
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss') AS FormattedDate;
- Output:
18/08/2024 14:35:20
- Use Case: Custom formatting of date and time for reporting purposes.
5. CONVERT()
- Purpose: Converts a date/time value to a different data type and format.
- Example:
SELECT CONVERT(VARCHAR, GETDATE(), 103) AS ConvertedDate;
- Output:
18/08/2024
- Use Case: Converting date/time to string in a specific format for display or processing.
6. DATEPART()
- Purpose: Returns a specific part of a date, such as year, month, day, hour, etc.
- Example:
SELECT DATEPART(YEAR, '2024-08-18') AS YearPart;
- Output:
2024
- Use Case: Extracting specific components of a date for analysis or calculation.
7. EOMONTH()
- Purpose: Returns the last day of the month for a given date, with an optional offset.
- Example:
SELECT EOMONTH('2024-08-18') AS EndOfMonth;
- Output:
2024-08-31
- Use Case: Finding the last day of the month, useful in financial calculations.
8. GETUTCDATE()
- Purpose: Returns the current date and time in UTC (Coordinated Universal Time).
- Example:
SELECT GETUTCDATE() AS CurrentUTCDateTime;
- Use Case: Storing or comparing timestamps in UTC for consistency across time zones.
9. SWITCHOFFSET()
- Purpose: Adjusts a datetimeoffset value to a new time zone offset.
- Example:
SELECT SWITCHOFFSET('2024-08-18 14:00:00 +02:00', '-05:00') AS NewDateTimeOffset;
- Output:
2024-08-18 07:00:00 -05:00
- Use Case: Converting between different time zones.
10. SYSDATETIME()
- Purpose: Returns the current date and time, including fractional seconds, of the SQL Server as a
datetime2
value. - Example:
SELECT SYSDATETIME() AS CurrentSysDateTime;
- Use Case: Getting precise date and time information, including fractional seconds, for high-precision applications.
11. ISDATE()
- Purpose: Checks if an expression is a valid date, time, or datetime value.
- Example:
SELECT ISDATE('2024-08-18') AS IsValidDate;
- Output:
1
(True) - Use Case: Validating date inputs before processing or storing them in the database.
These functions provide robust tools for managing, manipulating, and formatting date and time data in SQL Server.