Date Time Funtions

Analysis of time-based data.

Introduction

SQL logo

Date and Time functions in SQL are essential for several reasons, particularly when working with time-sensitive data. Here's why you need them:

  • Date and time functions allow for accurate analysis of time-based data.
  • Scheduling and Reporting: Many reports or processes are scheduled on specific dates or run periodically.
  • Date functions allow you to filter records based on time ranges.
  • Date and time functions help ensure data integrity by validating date entries.
  • Different systems and regions require different date formats.
  • Many applications require time calculations, such as working out age, tenure, or time left until a deadline.

Data types for date and time values

MS SQL provides various data types for storing and manipulating date and time values:

  • DATE: Stores the date part (YYYY-MM-DD).
  • TIME: Stores the time part (HH:MI:SS).
  • DATETIME: Stores both date and time.
  • DATETIME2: Similar to DATETIME but with more precision.
  • SMALLDATETIME: Less precision compared to DATETIME.
  • DATETIMEOFFSET: Includes date, time, and time zone offset.

Common Date and Time functions

  1. Getting the Current Date and Time

MS SQL offers functions to retrieve the current date and time.

  • GETDATE(): Returns the current date and time.
SELECT GETDATE() AS CurrentDateTime;
  • SYSDATETIME(): Returns the current date and time with higher precision.
SELECT SYSDATETIME() AS HighPrecisionCurrentDateTime;
  • CURRENT_TIMESTAMP: An ANSI-compliant alternative to GETDATE().
SELECT CURRENT_TIMESTAMP AS CurrentDateTime;
  1. Extracting Components of Date and Time

You often need to extract specific parts of a date or time, like the year, month, or hour. MS SQL provides several functions for this purpose:

  • YEAR(): Returns the year part of a date.
SELECT YEAR(GETDATE()) AS CurrentYear;
  • MONTH(): Returns the month part of a date.
SELECT MONTH(GETDATE()) AS CurrentMonth;
  • DAY(): Returns the day of the month.
SELECT DAY(GETDATE()) AS CurrentDay;
  • DATEPART(): Extracts a specific part of the date (e.g., year, quarter, day).
SELECT DATEPART(QUARTER, GETDATE()) AS CurrentQuarter;
  1. Date and Time Arithmetic

SQL Server allows you to perform arithmetic operations on date and time values, such as adding or subtracting dates.

  • DATEADD(): Adds a specified interval to a date.
SELECT DATEADD(DAY, 10, GETDATE()) AS DateAfterTenDays;

This example adds 10 days to the current date.

  • DATEDIFF(): Returns the difference between two dates.
SELECT DATEDIFF(DAY, '2024-01-01', GETDATE()) AS DaysSinceStartOfYear;

This returns the number of days between January 1, 2024, and today.

  • DATETIMEFROMPARTS(): Constructs a datetime value from its individual components.
SELECT DATETIMEFROMPARTS(2024, 12, 25, 12, 30, 0, 0) AS ChristmasLunch;
  1. Formatting Date and Time Values

Formatting is crucial when presenting date and time values in reports or converting them between different formats.

  • FORMAT(): Customizes the appearance of date and time.
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

This returns the date in the format YYYY-MM-DD.

  • CONVERT(): Converts between date formats.
SELECT CONVERT(VARCHAR, GETDATE(), 103) AS UKDateFormat;

Here, 103 specifies the British date format (DD/MM/YYYY).

  1. Working with Time Zones

For handling time zones, DATETIMEOFFSET is particularly useful.

  • SWITCHOFFSET(): Converts a DATETIMEOFFSET to a new time zone.
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00') AS TimeInEST;

This example converts the current system time to Eastern Standard Time.

  • TODATETIMEOFFSET(): Converts a datetime to DATETIMEOFFSET.
SELECT TODATETIMEOFFSET(SYSDATETIME(), '-05:00') AS DateTimeWithOffset;
  1. Parsing and Validating Date Strings

When dealing with user input or external data, you may need to validate or parse date strings.

  • TRY_PARSE(): Converts a string to date/time data type; returns NULL if invalid.
SELECT TRY_PARSE('2024-09-22' AS DATETIME) AS ParsedDate;
  • TRY_CONVERT(): Converts a string to another data type but handles errors more gracefully than CONVERT().
SELECT TRY_CONVERT(DATETIME, '22-09-2024', 105) AS ParsedDate;
  1. Date and Time Rounding

In some cases, you may need to round date and time values to the nearest minute, hour, or day.

  • DATEFROMPARTS(): Extracts only the date part, ignoring the time.
SELECT DATEFROMPARTS(2024, 09, 22) AS RoundedDate;
  • EOMONTH(): Returns the last day of the month for a given date.
SELECT EOMONTH(GETDATE()) AS EndOfMonth;

Advanced Date Functions

MS SQL also includes more advanced date functions for specific use cases.

  • ISDATE(): Checks if a value is a valid date.
SELECT ISDATE('2024-09-22') AS IsValidDate;
  • SYSDATETIMEOFFSET(): Returns the current date and time along with the time zone offset.
SELECT SYSDATETIMEOFFSET() AS CurrentDateTimeWithOffset;

Common Use Cases

  1. Filtering Data by Date Range

You might need to filter records based on a specific date range, such as finding all customers who signed up last month.

SELECT customer_id, SignupDate 
FROM Customers 
WHERE created_date BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE();
  1. Calculating Age

To calculate the age of a customer based on their birth date:

SELECT first_name+ ' ' +last_name, 
       DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age 
FROM Customers;

Conclusion

Understanding MS SQL’s date and time functions is critical for working with time-sensitive data. Whether it’s retrieving the current date, formatting it for reports, or calculating the time difference between events, these functions empower you to handle temporal data efficiently.

Asked in Interview

  • What are the most commonly used date and time functions in SQL?
  • How do you get the current date in SQL?
  • How would you calculate the difference between two dates?
  • How do you extract just the year from a date column?
  • Explain how you can add 30 days to a date?