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.

Advanced string functions in SQL Server

 Advanced string functions in SQL Server that are useful for complex string manipulation tasks:

1. STRING_AGG

• Purpose: Concatenates values from a group into a single string, with a specified separator.
• Example:

  SELECT STRING_AGG(column_name, ', ') AS concatenated_string
  FROM your_table;
  

• Use Case: Aggregating multiple rows of data into a single comma-separated string.

2. FORMAT

• Purpose: Returns a value formatted according to a specified format and culture.
• Example:

  SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS formatted_date;
  

• Use Case: Formatting dates, numbers, or other values into specific string representations.

3. STUFF

• Purpose: Deletes a specified length of characters and inserts another string into the original string.
• Example:

  SELECT STUFF('Hello World', 6, 5, 'SQL') AS result;
  

• Output: Hello SQL
• Use Case: Replacing part of a string with another string.

4. REVERSE

• Purpose: Reverses the order of characters in a string.
• Example:

  SELECT REVERSE('SQL Server') AS reversed_string;
  

• Output: revreS LQS
• Use Case: Checking palindromes or reversing strings for specific logic.

5. PATINDEX

• Purpose: Returns the starting position of the first occurrence of a pattern in a string.
• Example:

  SELECT PATINDEX('%Server%', 'SQL Server 2024') AS position;
  

• Output: 5
• Use Case: Finding the position of a substring using wildcard patterns.

6. TRANSLATE

• Purpose: Replaces a sequence of characters in a string with another sequence of characters.
• Example:

  SELECT TRANSLATE('1234-5678', '12345678', 'ABCDEFGH') AS translated_string;
  

• Output: ABCD-EFGH
• Use Case: Substituting characters in a string.

7. TRIM

• Purpose: Removes leading and trailing spaces or specified characters from a string.
• Example:

  SELECT TRIM('!.' FROM '...Hello World...') AS trimmed_string;
  

• Output: Hello World
• Use Case: Cleaning up strings by removing unwanted characters from both ends.

8. CHARINDEX

• Purpose: Returns the starting position of a specified substring within a string.
• Example:

  SELECT CHARINDEX('World', 'Hello World') AS position;
  

• Output: 7
• Use Case: Locating a substring within a string without using wildcards.

9. REPLACE

• Purpose: Replaces all occurrences of a specified substring with another substring.
• Example:

  SELECT REPLACE('Hello World', 'World', 'SQL') AS replaced_string;
  

• Output: Hello SQL
• Use Case: Replacing specific parts of a string.

10. LEFT and RIGHT

• Purpose: Extracts a specified number of characters from the left or right side of a string.
• Example:

  SELECT LEFT('SQL Server', 3) AS left_string, RIGHT('SQL Server', 6) AS right_string;
  

• Output: SQL, Server
• Use Case: Extracting substrings from the beginning or end of a string.

These advanced string functions are essential for complex text processing and manipulation tasks in SQL Server.