SQL Date Functions - Complete Beginner Tutorial

Extracting Date Parts with DATEPART()

The DATEPART() function extracts a specific component from a date, such as the year, month, day, hour, or minute.

• Syntax: DATEPART(part, date)
• Common parts include year, month, day, hour, minute, second.

Adding and Subtracting Dates with DATEADD()

DATEADD() lets you add or subtract a specified time interval from a date, useful for calculating future or past dates.

• Syntax: DATEADD(part, number, date)
• The 'number' can be positive (add) or negative (subtract).

Calculating Differences with DATEDIFF()

DATEDIFF() calculates the difference between two dates in terms of a specified part, such as days or months.

• Syntax: DATEDIFF(part, start_date, end_date)
• Returns an integer representing the difference.

Formatting Dates with FORMAT()

FORMAT() converts a date to a string in a specified format, improving readability or meeting reporting requirements.

• Syntax: FORMAT(date, format_string)
• Format strings can include patterns like 'yyyy-MM-dd' or 'MM/dd/yyyy'.

Example: Find Orders from the Last 7 Days

This query selects orders placed within the last 7 days using GETDATE() and DATEADD().

Example: Calculate Customer Age from Birthdate

Calculate age by finding the difference in years between the current date and the birthdate.