To convert VARCHAR data to date format, you can use STR_TO_DATE() −mysql> create table DemoTable1989    (    DueDate varchar(20)    ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1989 values('31/01/2015'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable1989 values('01/12/2018'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1989 values('25/10/2019'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select * from DemoTable1989;This will produce the following output −+------------+ | DueDate    | +------------+ | 31/01/2015 | ... Read More

You can use CASE statement −mysql> create table DemoTable1988    (    Value1 int,    Value2 int,    Price int    ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1988 values(10, 7, 500); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1988 values(7, 9, 400); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1988 values(8, 7, 200); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1988 values(7, 4, 300); Query OK, 1 row affected (0.16 sec)Display all records from the table using select ... Read More

To return only the month number, you can use DATE_FORMAT() -mysql> create table DemoTable1999    (    ArrivalDate timestamp    ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1999 values('2019-01-01 12:34:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1999 values('2019-12-31 10:04:00'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1999 values('2018-10-11 04:04:30'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable1999;This will produce the following output −+---------------------+ | ArrivalDate         ... Read More

For this, you can use UNION ALL. Let us first create a table −mysql> create table DemoTable1987    (    UserValue int    ); Query OK, 0 rows affected (2.90 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1987 values(4); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1987 values(5); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1987 values(6); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1987 values(7); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select * from DemoTable1987;This ... Read More

For this, you can use IN() along with ORDER BY clause. Let us first create a table −mysql> create table DemoTable1986    (    Number int    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1986 values(50); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(60); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(100); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(200); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(350); Query OK, 1 row ... Read More

For this, use STR_TO_DATE() along with DATE_FORMAT(). Let us first create a table −mysql> create table DemoTable1985    (    DueDate varchar(20)    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1985 values('Jan 2017'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1985 values('May 2018'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1985 values('Aug 2015'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1985;This will produce the following output −+----------+ | DueDate  | ... Read More

Let’s say you need to change the date and add years. For this, use UPDATE command along with DATE_ADD(). Let us first create a table −mysql> create table DemoTable1984    (    ShippingDate date    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1984 values('2014-01-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1984 values('2015-12-23'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1984 values('2017-10-31'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1984 values('2018-06-01'); Query OK, 1 row affected (0.00 sec)Display all ... Read More

To insert a row like this use the following syntax, wherein we are using CURRENT_TIMESTAMP −insert into yourTableName values(CURRENT_TIMESTAMP - INTERVAL ABS(RAND() * 100) DAY);To understand the above syntax, let us create a table −mysql> create table DemoTable1983    (    DueDate timestamp    ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1983 values(CURRENT_TIMESTAMP - INTERVAL ABS(RAND() * 100) DAY); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1983 values(CURRENT_TIMESTAMP - INTERVAL ABS(RAND() * 100) DAY); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1983 ... Read More

Let us first create a table −mysql> create table DemoTable1982    (    StudentId int    ,    StudentName varchar(20),    StudentAge int    ); Query OK, 0 rows affected (0.00 sec)Let us check the table engine type -mysql> show create table DemoTable1982;This will produce the following output −+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table         | Create Table                                                                                     ... Read More

For this, use UNION ALL along with SUM(). Let us create 5 tables −mysql> create table DemoTable1977    (    UP int    ); Query OK, 0 rows affected (0.00 sec) mysql> insert into DemoTable1977 values(10); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1977 values(20); Query OK, 1 row affected (0.00 sec) mysql> select * from DemoTable1977; +------+ | UP   | +------+ |   10 | |   20 | +------+ 2 rows in set (0.00 sec) mysql> create table DemoTable1978    (    UP int    ); Query OK, 0 rows affected (0.00 sec) mysql> ... Read More