To store such kind of dates where the day, month or both month & day are zero we must have to set mode of sql to allow_invalid_dates mode.mysql> set sql_mode = 'allow_invalid_dates'; Query OK, 0 rows affected (0.00 sec) mysql> insert into check_date(OrderDate) values('2017-00-00'); Query OK, 1 row affected (0.06 sec) mysql> select * from check_date; +-------------+ | OrderDate | +-------------+ | 2017-00-00 | +-------------+ 1 row in set (0.00 sec)Above query will insert the date in which both month & day values are zero.mysql> insert into check_date(Orderdate) values ('2017-00-05'); Query OK, 1 row affected (0.07 sec) ... Read More

In DATEDIFF() function only the date parts of the values are used in calculation hence we can use TIMESTAMPDIFF() function to calculate full 24 hour days between two specified dates.For example, if we want to find full 24hour days between ‘2017-05-27 11:59:00’ and 2017-05-23 12:00:00’ then following would be MySQL query −mysql> Select TIMESTAMPDIFF(DAY, '2017-05-23 12:00:00' , '2017-05-27 11:59:00'); +---------------------------------------------------------------------------+ | TIMESTAMPDIFF(DAY, '2017-05-23 12:00:00' , '2017-05-27 11:59:00')         | +---------------------------------------------------------------------------+ | 3                                                                         | +---------------------------------------------------------------------------+ 1 row in set (0.00 sec)

With the help of DATEDIFF(expr1, expr2) we can find the business days between two specified dates.For example, if we want to find business days between ‘2017-05-27’ and ‘2017-05-23’ then following would be MySQL query −mysql> Select DATEDIFF('2017-05-27','2017-05-23') AS 'Total Business Days'; +----------------------+ | Total Business Days  | +----------------------+ | 4                    | +----------------------+ 1 row in set (0.00 sec)

As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car2 (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car2 values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More

We can compute the date as follows −mysql> SET @year=2017, @week=15, @day=4; Query OK, 0 rows affected (0.00 sec)The above query will pass the value’2017’ ,’15’, ‘4’ in ‘year’, ’week’ and ‘day’ variables respectively. Then after applying the formula in the query below, we can get the date.mysql> SELECT Str_To_Date( Concat(@year,'-',@week,'-',If(@day=7,0,@day) ), '%Y-%U-%w' ) AS Date; +--------------+ | Date         | +--------------+ | 2017-04-13   | +--------------+ 1 row in set (0.00 sec)

As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car1’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car1 (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car1 values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More

We can calculate age in years from birthdate as follows −mysql> SET @dob = '1984-01-17'; Query OK, 0 rows affected (0.00 sec)This above query will pass the value’1984-01-17’ in the ‘dob’ variable. Then after applying the formula in the query below, we can get the age in years.mysql> Select Date_format( From_Days( To_Days(Curdate()) - To_Days(@dob) ), '%Y' ) + 0 AS ‘Age in years; +---------------+ | ‘Age in years’| +---------------+ |   33          | +---------------+ 1 row in set (0.00 sec)

After enabling the SQL MODE to ALLOW_INVALID_DATES, MySQL will also be able to store invalid dates in the table. The example is given below to understand it −mysql> Insert into order1234(ProductName, Quantity, Orderdate) values('B',500,'2015-11-31'); Query OK, 1 row affected (0.06 sec) mysql> Select * from order1234; +-------------+----------+--------------+ | ProductName | Quantity | OrderDate    | +-------------+----------+--------------+ | A           | 500      | 0000-00-00   | | B           | 500      | 2015-11-31   | +-------------+----------+--------------+ 2 rows in set (0.00 sec)We can see MySQL also inserts the invalid date in a table.

The response of MySQL on encountering out-of-range or invalid date will depend upon SQL MODE. If we have enabled ALLOW_INVALID_DATES mode then MySQL will convert the out of range values into all zeros (i.e. ‘0000:00:00 00:00:00’) and also stores the same in the table without producing any error or warning.For example, we can change SQL MODE as follows and then insert the out-of-range −mysql> set sql_mode = 'ALLOW_INVALID_DATES'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into order1234(productname, quantity, orderdate) values('A', 500, '999-05-100'); Query OK, 1 row affected, 1 warning (0.13 sec) mysql> Select * from order1234; ... Read More

As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More