Definitions:
The DATE function in SQL is used to manipulate and work with date values within a SQL query. It allows you to extract specific date parts, such as year, month, or day, or perform arithmetic operations on dates, such as adding or subtracting days.
There are several date formats that can be used with the DATE function in SQL, and the format you choose will depend on your specific needs and the requirements of your database. Some common date formats include:
- YYYY-MM-DD : This format uses four digits for the year, two digits for the month, and two digits for the day, separated by dashes. For example, “2023-04-12” represents April 12th, 2023.
- MM/DD/YYYY : This format uses two digits for the month, two digits for the day, and four digits for the year, separated by slashes. For example, “04/12/2023” represents April 12th, 2023.
- DD/MM/YYYY : This format uses two digits for the day, two digits for the month, and four digits for the year, separated by slashes. For example, “12/04/2023” represents April 12th, 2023.
- YYYYMMDD : This format uses four digits for the year, two digits for the month, and two digits for the day, with no separators. For example, “20230412” represents April 12th, 2023.
Commonly used date functions in SQL :
- CURRENT_TIMESTAMP : This function returns the current date & time.
Syntax :
SELECT CURRENT_TIMESTAMP;
Result :
- GETDATE() : This function returns the current date & time.
Syntax :
SELECT GETDATE();
Result :
- DATEADD : This function adds a specified number of intervals (days, months, years) to a date.
Syntax :
DATEADD(interval, number, date);
Example : Add 1 year to the given date
SELECT DATEADD(YEAR, 1, ‘2017/08/25’) AS DateAdd;
Result :
Example : Add 1 month to the given date
SELECT DATEADD(MONTH, 1, ‘2017/08/25’) AS Add_month;
Result :
Example : Subtract a quarter from the given date
SELECT DATEADD(QUARTER, -1, ‘2017/08/25’) AS Subtract_Quarter;
Result :
Example : Subtract a week from the given date
SELECT DATEADD(WEEK, -1, ‘2017/08/25’) AS Subtract_week;
Result :
Example : Add days to the given date
SELECT DATEADD(WEEKDAY, 2, ‘2017/08/25’) AS Add_weekday;
Result :
Example : Add hour to the given time
SELECT DATEADD(HOUR, 2, ‘2011-09-23 12:48:39’) AS Add_hour;
Result :
- DATEDIFF : This function calculates the difference between two dates in a specified interval (days, months, years).
Syntax :
DATEDIFF(interval, start_date, end_date);
Example :
SELECT DATEDIFF(Day, ‘2023-04-01’, ‘2023-04-12’);
Result :
- DATEPART : This function extracts a specific part of a date (year, month, day, hour, minute, second).
Syntax :
DATEPART(Interval, Date);
Example :
SELECT DATEPART(Year, ‘2017/08/25’) AS DatePartInt;
Result :
- DATENAME :
Example :
SELECT DATEName(DAYOFYEAR, ‘2011-09-23 12:48:39’) AS Day_num;
Result :
Example :
SELECT DATENAME(QUARTER, ‘2011-09-23 12:48:39’) AS Quarter_num;
Result :
Example :
SELECT DATENAME(MM, ‘2011-09-23 12:48:39’) AS Month_name;
Result :
- DATEFROMPARTS : This function formats a date or time value in a specific format.
Syntax :
DATEFROMPARTS ( year, month, day )
Example :
SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result;
Result :
