MySQL date time manipulation functions

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
MySQL numeric manipulation functions
MySQL numeric manipulation functions - Abs(), Cos(), Exp(), Rand(), Sin(), Sqrt(), Tan(), Mod(), Pi()
MySQL Group By
MySQL Group By is used along with aggregate functions to group the result-set by one or more columns.
MySQL Table
MySQL Table - What are the different tables present in MySQL?, How can we repair a MySQL table?, What are HEAP tables in MySQL?, Explain the capabilities of command ALTER table...
Post your comment