Compare date strings in MySQL

AmitDiwan
Updated on 22-Aug-2019 07:07:00
To compare date strings, use STR_TO_DATE() from MySQL.Let us first create a table −mysql> create table DemoTable712 (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ArrivalDate varchar(100) ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable712(ArrivalDate) values('10.01.2019'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable712(ArrivalDate) values('11.12.2018'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable712(ArrivalDate) values('01.11.2017'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable712(ArrivalDate) values('20.06.2016'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select ... Read More

MySQL query to get records after an interval of 8 months

AmitDiwan
Updated on 22-Aug-2019 07:06:51
For this, use INTERVAL 8 MONTH and fetch records 8 months from the current date −select *from yourTableName where yourColumnName>= (CURRENT_DATE() - INTERVAL 8 MONTH);Note − Let’s say the current date is: 2018-02-06Let us first create a table −mysql> create table DemoTable (StudentName varchar(100), AdmissionDate date); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', '2019-01-21'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Chris', '2019-10-04'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values('Robert', '2018-02-01'); Query OK, 1 row affected (0.16 sec) ... Read More

Implement WHERE IN vs OR in MySQL with similar example

AmitDiwan
Updated on 22-Aug-2019 07:05:48
The IN uses an index while OR does not use an index in MySQL.Let us first create a table −mysql> create table DemoTable711 (    Id int,    Name varchar(100) ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using insert command −mysql> insert into DemoTable711 values(100, 'Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable711 values(101, 'Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable711 values(102, 'Carol'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable711 values(103, 'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert ... Read More

Print maximum sum square sub-matrix of given size in C Program.

Sunidhi Bansal
Updated on 22-Aug-2019 07:04:58
Given a matrix of NxN find a sub matrix of MxM where M=1 such that addition of all the elements of matrix MxM is maximum. Input of matrix NxN can contain zero, positive and negative integer values.ExampleInput:    {{1, 1, 1, 1, 1},    {2, 2, 2, 2, 2},    {3, 3, 3, 3, 3},    {4, 4, 4, 4, 4},    {5, 5, 5, 5, 5} } Output:    4 4    5 5The above problem can be solved by a simple solution in which we can take whole matrix NxN, then find out all possible MxM matrix and ... Read More

Fix MySQL Database Error #1064?

AmitDiwan
Updated on 22-Aug-2019 07:02:02
The Database Error #1064 may occur due to incorrect syntax. For example, let’s say we are creating the below table −mysql> create table DemoTable    (       UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,       UserName varchar(100),       UserAge int,       UserAddress varchar(200),       UserCountryName varchar(100) ,       isMarried boolean,    );This will produce the following output i.e. error −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' ... Read More

MySQL stored procedure parameters don't seem to work with special character @?

AmitDiwan
Updated on 22-Aug-2019 06:58:36
You cannot give MySQL stored procedure parameter with @ in the beginning. You can give @ sign in user-defined variables.Following is the syntax −SET @yourVariableName=yourValue;Let us implement the above syntax to correctly place @sign −mysql> DELIMITER // mysql> CREATE PROCEDURE declare_Variable(IN StudentName varchar(100))    BEGIN       SET @Name=StudentName;       SELECT @Name;    END // Query OK, 0 rows affected (0.12 sec) mysql> DELIMITER ;Now you can call stored procedure with the help of CALL command −mysql> call declare_Variable('John Smith');This will produce the following output −+------------+ | @Name | +------------+ | John Smith | +------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.03 sec)

Is it possible to make an insert or an update in the same MySQL query?

AmitDiwan
Updated on 22-Aug-2019 06:56:16
Yes, use ON DUPLICATE KEY UPDATE. Let us first create a table −mysql> create table DemoTable(Id int NOT NULL PRIMARY KEY, Number int); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 190) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(2, 130) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(1, 190) ON DUPLICATE KEY UPDATE Number=Number+10; Query OK, 2 rows affected (0.14 sec) mysql> insert into DemoTable values(2, 130) ON DUPLICATE ... Read More

Print left rotation of array in O(n) time and O(1) space in C Program.

Sunidhi Bansal
Updated on 22-Aug-2019 06:54:19
We are given an array of some size n and multiple integer values, we need to rotate an array from a given index k.We want to rotate an array from a index k like −ExamplesInput: arr[] = {1, 2, 3, 4, 5}    K1 = 1    K2 = 3    K3 = 6 Output:    2 3 4 5 1    4 5 1 2 3    2 3 4 5 1AlgorithmSTART Step 1 -> Declare function void leftRotate(int arr[], int n, int k)    Declare int cal = k% n    Loop For int i=0 and i In ... Read More

Print k different sorted permutations of a given array in C Program.

Sunidhi Bansal
Updated on 22-Aug-2019 06:49:37
Given an array a[] containing N integers, the challenge is to print k different permutations of indices such that the values at those indices form a non-decreasing sequence. Print -1 if it is not possible.ExampleInput: arr[] = {2, 5, 6, 2, 2, 2, 2}, k = 4 Output:    0 3 4 5 6 1 2    3 0 4 5 6 1 2    0 3 4 5 6 1 2    3 0 4 5 6 1 2Sort the given array and keep track of the original indices of each element. That gives one required permutation. Now if ... Read More

Set the NULL values to 0 and display the entire column in a new column with MySQL SELECT

AmitDiwan
Updated on 22-Aug-2019 06:44:54
For this, use IFNULL(). Let us first create a table −mysql> create table DemoTable (Value int); Query OK, 0 rows affected (1.02 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> ... Read More
Advertisements