# Essential MySQL Functions
MySQL has many built-in functions. We will covering some important most used built-in functions; for a complete list refer to the online MySQL Reference Manual (http://dev.mysql.com/doc/).
> NOTE: As of now we will be going through only function and their output, as they would be self explanatory.
## Numeric Functions
```sql
SELECT ROUND(5.73)
```
6
```sql
SELECT ROUND(5.73, 1)
```
5.7
```sql
SELECT TRUNCATE(5.7582, 2)
```
5.75
```sql
SELECT CEILING(5.2)
```
6
```sql
SELECT FLOOR(5.7)
```
5
```sql
SELECT ABS(-5.2)
```
5.2
```sql
SELECT RAND() -- Generates a random floating point number b/w 0 & 1
```
## STRING Functions
```sql
SELECT LENGTH('sky')
```
3
```sql
SELECT UPPER('sky')
```
SKY
```sql
SELECT LOWER('sky)
```
sky
```sql
SELECT LTRIM(' sky')
```
sky
```sql
SELECT RTRIM('sky ')
```
sky
```sql
SELECT TRIM(' sky ')
```
sky
```sql
SELECT LEFT('Kindergarten', 4)
```
Kind
```sql
SELECT RIGHT('Kindergarten', 6)
```
garten
```sql
SELECT SUBSTRING('Kindergarten', 3, 5)
```
nderg
```sql
SELECT LOCATE('n','Kindergarten') -- LOCATE returns the first occurrence of a character or character string, if found, otherwise it returns 0
```
3
```sql
SELECT REPLACE('Kindergarten', 'garten', 'garden')
```
Kindergarden
```sql
SELECT CONCAT('first', 'last')
```
firstlast
## DATE Functions
```sql
SELECT NOW()
```
2021-10-21 19:59:47
```sql
SELECT CURDATE()
```
2021-10-21
```sql
SELECT CURTIME()
```
20:01:12
```sql
SELECT MONTH(NOW())
```
10
```sql
SELECT YEAR(NOW())
```
2021
```sql
SELECT HOUR(NOW())
```
13
```sql
SELECT DAYTIME(NOW())
```
Thursday
## Formatting Dates and Times
> In MySQL, the default date format is "YYYY-MM-DD", ex: "2025-05-12", MySQL allows developers to format it the way they want. We will discuss some of them.
```sql
SELECT DATE_FORMAT(NOW(), '%M %D %Y')
```
October 22nd 2021
```sql
SELECT DATE_FORMAT(NOW(), '%m %d %y')
```
10 22 21
```sql
SELECT DATE_FORMAT(NOW(), '%m %D %y')
```
10 22nd 21
```sql
SELECT TIME_FORMAT(NOW(), '%H %i %p')
```
14:11 PM
## Calculating Dates and Times
```sql
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) --return tomorrows date and time
```
2021-10-23 14:26:17
```sql
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR)
```
or
```sql
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)
```
> Both the queries will return the same output
2020-10-22 14:29:47
```sql
SELECT DATEDIFF('2021-09-08 09:00', '2021-07-07 17:00') -- It will return the difference in number of days, time won't be considered
```
63
```sql
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')
```
-120
MySQL has many built-in functions. We will covering some important most used built-in functions; for a complete list refer to the online MySQL Reference Manual (http://dev.mysql.com/doc/).
> NOTE: As of now we will be going through only function and their output, as they would be self explanatory.
## Numeric Functions
```sql
SELECT ROUND(5.73)
```
6
```sql
SELECT ROUND(5.73, 1)
```
5.7
```sql
SELECT TRUNCATE(5.7582, 2)
```
5.75
```sql
SELECT CEILING(5.2)
```
6
```sql
SELECT FLOOR(5.7)
```
5
```sql
SELECT ABS(-5.2)
```
5.2
```sql
SELECT RAND() -- Generates a random floating point number b/w 0 & 1
```
## STRING Functions
```sql
SELECT LENGTH('sky')
```
3
```sql
SELECT UPPER('sky')
```
SKY
```sql
SELECT LOWER('sky)
```
sky
```sql
SELECT LTRIM(' sky')
```
sky
```sql
SELECT RTRIM('sky ')
```
sky
```sql
SELECT TRIM(' sky ')
```
sky
```sql
SELECT LEFT('Kindergarten', 4)
```
Kind
```sql
SELECT RIGHT('Kindergarten', 6)
```
garten
```sql
SELECT SUBSTRING('Kindergarten', 3, 5)
```
nderg
```sql
SELECT LOCATE('n','Kindergarten') -- LOCATE returns the first occurrence of a character or character string, if found, otherwise it returns 0
```
3
```sql
SELECT REPLACE('Kindergarten', 'garten', 'garden')
```
Kindergarden
```sql
SELECT CONCAT('first', 'last')
```
firstlast
## DATE Functions
```sql
SELECT NOW()
```
2021-10-21 19:59:47
```sql
SELECT CURDATE()
```
2021-10-21
```sql
SELECT CURTIME()
```
20:01:12
```sql
SELECT MONTH(NOW())
```
10
```sql
SELECT YEAR(NOW())
```
2021
```sql
SELECT HOUR(NOW())
```
13
```sql
SELECT DAYTIME(NOW())
```
Thursday
## Formatting Dates and Times
> In MySQL, the default date format is "YYYY-MM-DD", ex: "2025-05-12", MySQL allows developers to format it the way they want. We will discuss some of them.
```sql
SELECT DATE_FORMAT(NOW(), '%M %D %Y')
```
October 22nd 2021
```sql
SELECT DATE_FORMAT(NOW(), '%m %d %y')
```
10 22 21
```sql
SELECT DATE_FORMAT(NOW(), '%m %D %y')
```
10 22nd 21
```sql
SELECT TIME_FORMAT(NOW(), '%H %i %p')
```
14:11 PM
## Calculating Dates and Times
```sql
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) --return tomorrows date and time
```
2021-10-23 14:26:17
```sql
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR)
```
or
```sql
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)
```
> Both the queries will return the same output
2020-10-22 14:29:47
```sql
SELECT DATEDIFF('2021-09-08 09:00', '2021-07-07 17:00') -- It will return the difference in number of days, time won't be considered
```
63
```sql
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')
```
-120