SQL DATE Functions

SQL DATE functions are built-in functions that can be used to manipulate and format dates and times in SQL. These functions can be very useful when working with date and time data in databases, allowing you to perform calculations, comparisons, and formatting on dates and times.

Here are some of the most commonly used SQL DATE functions:

CURRENT_TIMESTAMP This function returns the current date and time in the database server’s timezone.

-- Get the current date and time
SELECT CURRENT_TIMESTAMP;

CURRENT_TIMEZONE This function returns the current timezone offset of the database server.

-- Get the current timezone offset
SELECT CURRENT_TIMEZONE(); 

DATEADD This function is used to add or subtract a specified number of date or time units from a given date.

-- Add 7 days to a given date
SELECT DATEADD(day, 7, '2022-04-01');

DATEDIFF This function is used to calculate the difference between two dates or times.

-- Calculate the number of days between two dates
SELECT DATEDIFF(day, '2022-04-01', '2022-04-15');

DATEPART This function is used to extract a specific part of a date or time, such as the year, month, day, or hour.

-- Extract the year from a given date
SELECT DATEPART(year, '2022-04-01');

GETDATE This function returns the current date and time in the system’s timezone.

-- Get the current date and time in the system's timezone
SELECT GETDATE();

ISDATE This function is used to determine if a given value is a valid date or time.

-- Check if a given value is a valid date or time
SELECT ISDATE('2022-04-01');

DAY This function is used to extract the day of the month from a given date.

-- Extract the day of the month from a given date
SELECT DAY('2022-04-01');

MONTH This function is used to extract the month from a given date.

-- Extract the month from a given date
SELECT MONTH('2022-04-01');

YEAR This function is used to extract the year from a given date.

-- Extract the year from a given date
SELECT YEAR('2022-04-01');

DATEFROMPARTS This function allows you to create a date value by specifying its components. The DATEFROMPARTS function takes three arguments: the year, the month, and the day. By providing these components, you can construct a valid date.

-- Construct a valid date. 
SELECT DATEFROMPARTS(2023, 11, 27);

EOMONTH The function is used to calculate the last day of a month, given a specified date and a number of months to offset. This function is particularly useful for performing date-based calculations and generating reports that involve monthly data.

-- Calculate the last day of the current month 
SELECT EOMONTH(GETDATE()) AS LastDayOfMonth;

These SQL DATE functions can be very useful for a variety of applications, such as calculating the age of a person based on their birthdate, determining the number of days between two dates, or formatting dates for display purposes. By using these functions, you can easily manipulate and work with date and time data in SQL, making it easier to extract meaningful insights from your databases.