Class Notes

Class: XII

Subject: Informatics Practices

Class Notes

Date:07-09-2020

Topic: 6. MySQL Functions.

Date and Time Functions

Date and Time Functions operate on values of the DATE datatype.

1. CURDATE()/CURRENT_DATE() 2. DATE() 3. MONTH() 4. MONTHNAME() 5. DAY() 6. YEAR() 7. DAYNAME() 8. DAYOFMONTH() 9. DAYOFWEEK() 10. DAYOFYEAR() 11. NOW() 12. SYSDATE()

1. CURDATE()/CURRENT_DATE():

This function returns the current date of the system.

It returns the current date as a value in `YYYY-MM-DD' or YYYYMMDD format.

mysql> select curdate(); +------------+ | curdate() | +------------+ | 2020-08-24 | +------------+

2. DATE()

This function extracts the date part of a date or datetime expression.

mysql> select date('2020-05-09 02:34:45');

+-----------------------------+

| date('2020-05-09 02:34:45') |

+-----------------------------+

| 2020-05-09

|

+-----------------------------+

3. MONTH()

This function returns the month from the date passed.

mysql> select month('2020-08-15 09:07:32');

+------------------------------+

| month('2020-08-15 09:07:32') |

+------------------------------+

|

8 |

+------------------------------+

4. MONTHNAME()

This function returns the name of the month for a given date.

mysql> select monthname('2020-08-15');

+-------------------------+

| monthname('2020-08-15') |

+-------------------------+

| August

|

+-------------------------+

5. DAY()

This function returns the day part of the date.

mysql> select day('2020-08-15');

+-------------------+

| day('2020-08-15') |

+-------------------+

|

15 |

6. YEAR()

This function returns the year part of a date.

mysql> select year('2020-08-15');

+--------------------+

| year('2020-08-15') |

+--------------------+

|

2020 |

7. dayname(Date) This function returns the name of weekday.

mysql> select dayname('2020-08-15');

+-----------------------+

| dayname('2020-08-15') |

+-----------------------+

| Saturday

|

+-----------------------+

8. DAYOFMONTH()

This function returns the day of month. It returns the day of the month for date in the range 1 to 31.

mysql> select dayofmonth('2020-08-15');

+--------------------------+

| dayofmonth('2020-08-15') |

+--------------------------+

|

15 |

+--------------------------+

9. DAYOFWEEK()

Returns the weekly index for date(1=Sunday, 2=Monday ...7=Saturday )

mysql> select dayofweek('2020-08-5');

+------------------------+

| dayofweek('2020-08-5') |

+------------------------+

|

4 |

mysql> select dayofweek('2020-08-25');

+-------------------------+

| dayofweek('2020-08-25') |

+-------------------------+

|

3 |

10. DAYOFYEAR()

IT returns the day of the year for date; in the range 1 to 366.

mysql> select dayofyear('2020-12-25');

+-------------------------+

| dayofyear('2020-12-25') |

+-------------------------+

|

360 |

mysql> select dayofyear('2020-12-31');

+-------------------------+

| dayofyear('2020-12-31') |

+-------------------------+

|

366 |

11. NOW()

This function returns the current date and time.

mysql> select now();

+---------------------+

| now()

|

+---------------------+

| 2020-08-24 19:17:57 |

+---------------------+

12. SYSDATE()

This function returns the time at which function executes.

mysql> select sysdate(); +---------------------+ | sysdate() | +---------------------+ | 2020-08-24 19:19:11 | +---------------------+

Difference between NOW() and SYSDATE()

mysql> select sysdate(),sleep(2), sysdate();

+---------------------+----------+---------------------+

| sysdate()

| sleep(2) | sysdate()

|

+---------------------+----------+---------------------+

| 2020-08-24 19:19:29 |

0 | 2020-08-24 19:19:31 |

+---------------------+----------+---------------------+

mysql> select NOW(),sleep(2), NOW();

+---------------------+----------+---------------------+

| NOW()

| sleep(2) | NOW()

|

+---------------------+----------+---------------------+

| 2020-08-24 19:20:58 | 0 | 2020-08-24 19:20:58 |

+---------------------+----------+---------------------+

Dear students do all the Exercise questions of this chapter in your IP notebook. For your reference some solutions are given here:

Q 7:

mysql> select Monthname(cycledate) as 'Month', dayofmonth(Last_day(cycledate))-dayofmonth(cycledate)

as 'Days between payday and first day of the next month' from payday; +----------------+------------------------------------------------------------------------+

| Month | Days between payday and first day of the next month |

+----------------+-------------------------------------------------------------------------+

| September |

27 |

| April

|

17 |

| August |

28 |

+----------------+-------------------------------------------------------------------------+

Q. 10

mysql> select concat(firstname,lastname) as 'Name', concat('(',substr(phone,1,3),')',' ',substr(phone,4,3),'-',

substr(phone,7)) from address;

+-------------------+-----------------------------------------------------------------------------------------------+

| Name

| concat('(',substr(phone,1,3),')',' ',substr(phone,4,3),'-',substr(phone,7)) |

+-------------------+-----------------------------------------------------------------------------------------------+

| RaviGarg | (989) 001-2345

|

| ManishGupta | (985) 671-2789

|

| RoshniVerma | (885) 672-789

|

+-------------+------------------------------------------------------------------------------------------------------+

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download