SQL NULL values represent missing unknown data.

A table column can hold NULL values.

+—–+——+——-+
| uid | name | Email |
+—–+——+——-+
|   1 | AAAA |       |
|   2 | BBBB |       |
|   3 | CCCC |       |
|   4 | DDDD |       |
|   5 | EEEE |       |
+—–+——+——-+

SQL DATE_FORMAT() is used to display date/time data in different formats.

Example

mysql> SELECT DATE_FORMAT(NOW(),’%Y-%m-%D’);
+——————————-+
| DATE_FORMAT(NOW(),’%Y-%m-%D’) |
+——————————-+
| 2007-05-30th                  |
+——————————-+
1 row in set (0.00 sec)

Different formats

%a Abbreviated weekday name
%b Abbreviated month name
%c Month, numeric
%D Day of month with English suffix
%d Day of month, numeric (00-31)
%e Day of month, numeric (0-31)
%f Microseconds
%H Hour (00-23)
%h Hour (01-12)
%I Hour (01-12)
%i Minutes, numeric (00-59)
%j Day of year (001-366)
%k Hour (0-23)
%l Hour (1-12)
%M Month name
%m Month, numeric (00-12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss AM or PM)
%S Seconds (00-59)
%s Seconds (00-59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00-53) where Sunday is the first day of week
%u Week (00-53) where Monday is the first day of week
%V Week (01-53) where Sunday is the first day of week, used with %X
%v Week (01-53) where Monday is the first day of week, used with %x
%W Weekday name
%w Day of the week (0=Sunday, 6=Saturday)
%X Year of the week where Sunday is the first day of week, four digits, used with %V
%x Year of the week where Monday is the first day of week, four digits, used with %v
%Y Year, four digits
%y Year, two digits

SQL  DATEDIFF() function returns the time between two dates.

Table – orders

+—–+—–+——-+———————+———————+
| oid | uid | items | date                | delivery_date       |
+—–+—–+——-+———————+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 | 2007-05-24 00:00:00 |
+—–+—–+——-+———————+———————+

Example

mysql> SELECT DATEDIFF(delivery_date,date) AS days FROM orders;
+——+
| days |
+——+
|    8 |
+——+
1 row in set (0.03 sec)

SQL DATE_SUB() function subtracts a specified time interval from a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_SUB(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_SUB(date, INTERVAL 4 DAY) |
+——————————–+
| 20007-05-12 00:00:00            |
+——————————–+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL DATE_ADD() function adds a specified time interval to a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_ADD(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_ADD(date, INTERVAL 4 DAY) |
+——————————–+
| 2007-05-20 00:00:00            |
+——————————–+
1 row in set (0.01 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL EXTRACT() return a single part of a date/time(likes year, month, day, hour, minute)

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT EXTRACT(YEAR FROM date) FROM orders;
+————————-+
| EXTRACT(YEAR FROM date) |
+————————-+
|                    2007 |
+————————-+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL DATE() function extracts the date part of a date.

Table  – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE(date) as bill_date from orders ;
+————+
| bill_date  |
+————+
| 2007-05-16 |
+————+
1 row in set (0.00 sec)

SQL CURTIME() returns the current time.

Example

mysql> SELECT CURTIME();
+———–+
| CURTIME() |
+———–+
| 15:28:57  |
+———–+
1 row in set (0.01 sec)