Explain the following functions with an example.
AddDate(), AddTime(),CurDate(),CurTime(),Date(), DateDiff(), Date_Add(), Date_format()
1. AddDate() - This function performs data calculation.
addDate(date, INTERVAL, expr unit);
Example: Add 30 days to the date
Select DATE_ADD(‘2008-12-09’, INTERVAL 31 DAY);
Output:2008-01-10
2. AddTime(exp1, exp2) : This function adds expression 2 to 1 and returns result.
Example:SELECT AddTime(‘2008-12-31 23:59:59.999999’ , 1 1:1:1.000002);
Output:‘2009-01-02 01:01:01:000001’
3. CurDate() : Returns the current date. The date is returned either in YYYY-MM-DD format or YYYYMMDD format depending whether the function is used in a string or numeric context.
Example:Select CURDATE();
Output:2008-12-09
4. CurTime() : Returns the current time. The date is returned either in HH:MM:SS format or HH:MM:SS.uuuuuu format depending whether the function is used in a string or numeric context.
Example:Select CURTIME();
Output:23:23:25
5. Date() - Extracts the date part of an expression
Example:Select Date(‘2008-12-09 23:11:12’);
Output:2008-12-09
6. DateDiff() : Returns expr1 MINUS expr2 expressed as a value in days from one date to the other. Only the date parts of the values are used in the calculation
Example:Select DateDiff(‘2008-12-09 23:11:12’, ‘2008-12-10’);
Output:1
7. Date_format() : Formats the date value according to the format string.
Example : here, the format string can take various forms like W for Day of week, %T as time etc
SELECT DATE_FORMAT(‘2008-09-12 23:10:11’, %W, %M, %Y);
Output:‘Tuesday December 2008’
Explain the ways to find the current date using MySQL. Give example for each.
1. CURRDATE() - Returns the current date in YYYY-MM-DD format.
Select currdate();
SELECT CURRENT_DATE(); is a synonym for above.
2. CURTIME(); - Returns the current time in HH:MM:SS
Select currtime();
SELECT CURRENT_TIME(); is a synonym for above
