MySQLi Articles - Page 326 of 341

It can be understood with the help of following example in which we are using the following data from the table named ‘detail_bday’ −mysql> Select * from detail_bday; +----+---------+------------+ | Sr | Name    | Birth_Date | +----+---------+------------+ | 1  | Saurabh | 1990-05-12 | | 2  | Raman   | 1993-06-11 | | 3  | Gaurav  | 1984-01-17 | | 4  | Rahul   | 1993-06-11 | +----+---------+------------+ 4 rows in set (0.00 sec)Now, in the following two ways we can search records using the date −mysql> Select * from detail_bday Where Birth_Date = '1993-06-11'; +----+-------+------------+ | Sr | ... Read More

As the name suggests, enabling ALLOW_INVALID_DATES SQL mode will allow us to store invalid dates in the table. The example is given below to understand it −Examplemysql> SET sql_mode = ALLOW_INVALID_DATES; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> Insert Into detail_bday(Name, Birth_Date) values('Sonia', '1993-11-31'); Query OK, 1 row affected (0.09 sec) mysql> Insert Into detail_bday(Name, Birth_Date) values('Ram', '0000-00-00'); Query OK, 1 row affected (0.10 sec) mysql> Select * from detail_bday; +----+---------+------------+ | Sr | Name    | Birth_Date | +----+---------+------------+ | 1  | Saurabh | 1990-05-12 | | 2  | Raman   | 1993-06-11 ... Read More

As we know that the default format for MySQL DATE data type is “YYYY-MM-DD” and in this format, there is no possibility to store the time value. But still, if we store date along with time value in a column having DATE data type then MySQL will show a warning and store only the date value in that column. Following example will exhibit it −Examplemysql> Create table date_time_test(date DATE); Query OK, 0 rows affected (0.44 sec) mysql> Insert into date_time_test(date) values(‘2017-09-09 09:34:21’); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> Select * from date_time_test; +------------+ | ... Read More

Existing values of the row can be used to provide new values in the SET clause if that row matches the WHERE clause in an UPDATE statement. Following is the example to demonstrate it.ExampleSuppose we have a table named ‘tender’ as follows −mysql> Select * from tender; +-----------+---------+------+ | tender_id | company | rate | +-----------+---------+------+ | 200       | ABC     | 1000 | | 300       | ABD     | 6000 | | 301       | ABE     | 7000 | | 302       | ABF   ... Read More

The order of columns in the SET clause of UPDATE statement is important because MySQL provides us the updated value on columns names used in an expression. Yes, it will make big difference in the result set returned by MySQL. Following is an example to make it clear −ExampleIn this example, we are having a table ‘tender’. First, we will write UPDATE statement by using ‘tender_id’ as the first and ‘rate’ as the second column in SET clause and then we will write UPDATE statement by using ‘rate’ as the first and ‘tender_id’ as the second column on table ‘tender’.mysql> ... Read More

Following is a MySQL function which calculates the duration in years, months, days, hours, minutes and seconds between two dates.mysql> DROP FUNCTION IF EXISTS Duration; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP FUNCTION IF EXISTS Label123; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DELIMITER // mysql> CREATE FUNCTION Duration( dtd1 datetime, dtd2 datetime ) RETURNS CHAR(128)    -> BEGIN    -> DECLARE yyr, mon, mmth, dy, ddy, hhr, m1, ssc, t1 BIGINT;    -> DECLARE dtmp DATETIME;    -> DECLARE t0 TIMESTAMP;    -> SET yyr = TIMESTAMPDIFF(YEAR, dtd1, dtd2);   ... Read More

If the specified format string and date string did not match then MySQL will return NULL value as output along with a warning. Following is an example to understand the same −mysql> Select STR_TO_DATE('20172810', '%Y, %d%m'); +------------------------------------+ | STR_TO_DATE('20172810', '%Y, %d%m') | +------------------------------------+ | NULL                               | +------------------------------------+ 1 row in set, 1 warning (0.00 sec)The query above returns NULL as output because the format string is having a comma (, ) after %Y but date string is not having any comma after 2017.mysql> Show Warnings\G ... Read More

If we pass an invalid string as an argument to STR_TO_DATE() function then MySQL will return NULL as output along with a warning. Following is an example to understand the same −mysql> Select STR_TO_DATE('20173210', '%Y%d%m'); +-----------------------------------+ | STR_TO_DATE('20173210', '%Y%d%m') | +-----------------------------------+ | NULL                              | +-----------------------------------+ 1 row in set, 1 warning (0.00 sec)In the query above the string value is invalid because of wrong (32) day value. Hence it returns NULL values and a warning which is given below.mysql> Show warnings\G *************************** 1. row ***************************   ... Read More

At the time of creation of a table, if any column is defined with default values then by using the keyword ‘DEFAULT’ in the INSERT statement, we can take default value for that column. For example, we have created a table ‘employee’ with a default value of column ‘DOJ’ as follows −mysql> Create table employee(id int, name varchar(20), doj date DEFAULT '2005-01-01'); Query OK, 0 rows affected (0.09 sec) mysql> Insert into employee(id, name, doj) values(1, ’Aarav’, DEFAULT); Query OK, 1 row affected (0.03 sec) mysql> select * from employee; +------+------------+---------------+ | id   | name     ... Read More

As we know that we can convert a time of datetime value to an integer by adding 0(+0) to them. In a similar way, we can convert the datetime value returned by FROM_UNIXTIME() function in numeric format. The following example will clarify it more −mysql> Select FROM_UNIXTIME(1555033470)+0 AS 'Date in Numeric Format'; +------------------------+ | Date in Numeric Format | +------------------------+ | 20190412071430.000000  | +------------------------+ 1 row in set (0.00 sec)After adding 0 (+0) to datetime value MySQL returns the numeric value up to 6 digits microseconds.