For this, you can use SUBSTR() method. Let us first create a table:mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(20)); Query OK, 0 rows affected (1.31 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(FirstName) values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(FirstName) values('Carol'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(FirstName) values('Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(FirstName) values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(FirstName) values('David'); ... Read More

To get today’s date in (YYYY-MM-DD) format in MySQL, you can use CURDATE().Following is the query to get the current date:mysql> SELECT CURDATE();This will produce the following output:+------------+ | CURDATE() | +------------+ | 2019-04-09 | +------------+ 1 row in set (0.00 sec)You can also use NOW() for this. Following is the query:mysql> SELECT DATE(NOW());This will produce the following output+-------------+ | DATE(NOW()) | +-------------+ | 2019-04-09 | +-------------+ 1 row in set (0.00 sec)

To apply MySQL query to each table in a database, you can use INFORMATION_SCHEMA.TABLES. Following is the syntax −SELECT SUM(TABLE_ROWS) AS anyAliasName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=yourDatabaseName;Let us implement the above syntax to query each table in a database.mysql> SELECT SUM(TABLE_ROWS) AS Total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= DATABASE();This will produce the Following output −+-------+ | Total | +-------+ | 1666 | +-------+ 1 row in set (0.01 sec)

Use CAST() in MAX() to get max id from varchar type and values in numeric. Let us first create a table. Here, we have a column with varchar type −mysql> create table DemoTable (    UserMarks varchar(20) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('77'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('98'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('45'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('56'); Query OK, 1 row affected (0.18 ... Read More

You need to use GROUP BY with COUNT(*) for this to group the values and display the count eliminating multiple values. Let us first create a table:mysql> create table DemoTable (Value int); Query OK, 0 rows affected (0.55 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

Let us first create a table in which one of the column is of datetime type;mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ShippingDate datetime ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command.mysql> insert into DemoTable(ShippingDate) values('2019-03-01 05:45:32'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(ShippingDate) values('2019-04-13 11:34:56'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(ShippingDate) values('2019-03-15 04:45:23'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(ShippingDate) values('2019-04-11 12:10:02'); Query OK, 1 row affected (0.17 sec)Following is the ... Read More

Let us first create a table with one column as datetime and another wherein the days are stored:mysql> create table DemoTable (    ShippingDate datetime,    CountOfDate int ); Query OK, 0 rows affected (0.52 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('2018-01-31', 6); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('2016-12-01', 15); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('2016-12-01', 10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2016-12-01', 5); Query OK, 1 row affected (0.17 sec) ... Read More

You can use ALTER command to convert Integer into Varchar. Let us first create a tablemysql> create table DemoTable (    UserId int,    UserFirstName varchar(20),    UserLastName varchar(20),    UserAge int ); Query OK, 0 rows affected (0.73 sec)Now check the description of table using DESC command:mysql> desc DemoTable;This will produce the following output −+---------------+-------------+------+-----+---------+-------+ | Field         | Type        | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | UserId        | int(11)     | YES | | NULL | ... Read More

If you’re using InnoDB engine, then you cannot set auto_increment value of tables to lower value. You need to change your engine from InnoDB to MyISAM.Note: The engine MyISAM allows you to set lower value. Here, we are using the same.According to the official documents:You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than ... Read More

To convert string to 24 hour datetime format in MySQL, you can use STR_TO_DATE() method. With that use the following format for datetime as the parameter:'%Y-%m-%d %H:%i:%s'Following is the syntaxSELECT STR_TO_DATE(yourColumnName, '%Y-%m-%d %H:%i:%s') FROM yourTableName;Let us first create a table:mysql> create table DemoTable (ArrivalDate varchar(200)); Query OK, 0 rows affected (0.57 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values('2019-01-31 15:45:23'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('2012-12-12 20:30:26'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('2016-06-07 21:04:05'); Query OK, 1 row ... Read More