Top date and time functions in SQL Server

 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.