Microsoft SQL Server

In Microsoft SQL Server, date functions are used to manipulate and retrieve information from date and time data types. These functions allow you to format dates, calculate differences between dates, and extract parts of a date, such as the year or month. For beginners, mastering date functions is crucial for working with time-sensitive data such as orders, sales, or customer records. Understanding how to perform date operations will help you analyze data over specific periods or track trends over time.

Below is a detailed explanation of the most commonly used date functions, examples, and best practices.

1. GETDATE() – Current Date and Time

The GETDATE() function returns the current system date and time.

SELECT GETDATE() AS CurrentDateTime;

This query returns the current date and time from the server.

2. DATEADD() – Adding or Subtracting Time

The DATEADD() function adds or subtracts a specified interval (days, months, years, etc.) to/from a given date.

SELECT DATEADD(interval, number, date);

Example:

SELECT DATEADD(DAY, 7, GETDATE()) AS NextWeek;

This query adds 7 days to the current date and returns the date for the next week.

3. DATEDIFF() – Difference Between Dates

The DATEDIFF() function calculates the difference between two dates in terms of a specified interval (days, months, years, etc.).

SELECT DATEDIFF(interval, start_date, end_date);

Example:

SELECT DATEDIFF(DAY, OrderDate, GETDATE()) AS DaysSinceOrder
FROM Orders;

This query calculates the number of days since each order was placed.

4. FORMAT() – Formatting Dates

The FORMAT() function allows you to format a date or time value into a specified format (e.g., YYYY-MM-DD, DD/MM/YYYY).

SELECT FORMAT(date, 'format_string') AS FormattedDate;

Example:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS FormattedDate;

This query formats the current date in YYYY-MM-DD format.

5. DATEPART() – Extracting Part of a Date

The DATEPART() function extracts a specific part of a date, such as the year, month, or day.

SELECT DATEPART(part, date) AS PartOfDate;

Example:

SELECT DATEPART(YEAR, GETDATE()) AS CurrentYear;

This query returns the current year from the system date.

6. YEAR(), MONTH(), and DAY() – Extracting Year, Month, and Day

SQL Server also provides simpler functions to extract the year, month, or day from a date.

SELECT YEAR(date) AS Year, MONTH(date) AS Month, DAY(date) AS Day;

Example:

SELECT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth
FROM Orders;

This query retrieves the year and month from each order date.

7. EOMONTH() – End of Month

The EOMONTH() function returns the last day of the month for a given date.

SELECT EOMONTH(date) AS EndOfMonth;

Example:

SELECT EOMONTH(GETDATE()) AS EndOfCurrentMonth;

This query returns the last day of the current month.

8. Best Practices for Using Date Functions

1. Use GETDATE() to Capture Current Date and Time – Use GETDATE() to get the current system date and time for tracking timestamps, such as when an order was placed.

   SELECT CustomerName, GETDATE() AS OrderTimestamp
   FROM Customers;

2. Use DATEADD() to Calculate Future or Past Dates – Use DATEADD() to calculate future or past dates, such as adding days, months, or years to a date.

   SELECT DATEADD(MONTH, 1, GETDATE()) AS NextMonth;

3. Use DATEDIFF() to Measure Date Differences – Use DATEDIFF() to calculate the difference between two dates, such as measuring how many days have passed since an order was placed.

   SELECT DATEDIFF(MONTH, HireDate, GETDATE()) AS MonthsEmployed
   FROM Employees;

4. Format Dates with FORMAT() for Readability – Use FORMAT() to convert dates into user-friendly formats, especially for reports and user interfaces.

   SELECT FORMAT(HireDate, 'dd/MM/yyyy') AS FormattedHireDate
   FROM Employees;

5. Extract Specific Parts of a Date with DATEPART() – Use DATEPART() when you need to extract specific components, such as the year or month, for filtering or grouping data.

   SELECT EmployeeID, DATEPART(YEAR, HireDate) AS HireYear
   FROM Employees;

6. Handle End-of-Month Calculations with EOMONTH() – Use EOMONTH() to retrieve the last day of the month for tasks like calculating billing cycles or deadlines.

   SELECT EOMONTH(GETDATE(), 1) AS EndOfNextMonth;

By mastering these date functions in SQL Server, you will be able to efficiently handle, format, and analyze date and time data, which is crucial for working with time-sensitive data in applications like sales, product lifecycle management, and reporting.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT YEAR(order_date), MONTH(order_date)
,count(*) as monthly_order_count
FROM act_order
GROUP BY YEAR(order_date), MONTH(order_date);

SELECT TOP 5 DATEPART(HOUR,order_date)
,count(*) as order_count
FROM act_order
GROUP BY DATEPART(HOUR,order_date)
ORDER BY count(*) DESC;

SELECT TOP 3 YEAR(GETDATE()) - birth_year as age
,count(*) as customer_count
FROM org_client
WHERE gender= 'M'
GROUP BY YEAR(GETDATE()) - birth_year
ORDER BY YEAR(GETDATE()) - birth_year DESC;