We can convert JS date time to MySQL datetime with the help of toISOString() function.Let us see an example of JavaScript.Example Live Demo           Web Page Design                document.writeln(new Date().toISOString().slice(0, 19).replace('T', ' '));              Current Date is displayed above... OutputThe following is the output.2018-11-23 11:14:38 Current Date is displayed above...

We can add 1 day to the date with the help of DATE_ADD() function. Creating a table. mysql> create table Add1DayDemo -> ( -> id int, -> MyDate datetime not null -> ); Query OK, 0 rows affected (1.06 sec) Insert some records. mysql> insert into Add1DayDemo values(1, now()); Query OK, 1 row affected (0.08 sec) mysql> insert into Add1DayDemo values(2, date_add(now(), interval 5 day)); Query OK, 1 row affected (0.16 sec) To display all records. mysql> select *from Add1DayDemo; ... Read More

The following is the syntax to get the current time zone of MySQL. mysql> SELECT @@global.time_zone, @@session.time_zone; The following is the output. +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.00 sec) The above just returns “SYSTEM” because MySQL is set for system time zones. Alternately, we can get the current time zone with the help of now() function. Let us first ... Read More

To make an existing field unique in MySQL, we can use the ALTER command and set UNIQUE constraint for the field. Let us see an example. First, we will create a table. mysql> create table AddingUnique -> ( -> Id int, -> name varchar(100) -> ); Query OK, 0 rows affected (0.44 sec) Syntax to add UNIQUE to an existing field. alter table yourTableName add UNIQUE(yourColumnName); Applying the above syntax in order to add UNIQUE to column ‘name’. mysql> alter table AddingUnique add ... Read More

Whenever a table is present in a project with a single database, we can do database schema changes using schema version or migration. It aims to keep track of database schema changes or structural changes. The table creation to keep track of schema changes. mysql> create table SchemaDatabaseMethodDemo -> ( -> `WhenTime` timestamp not null default CURRENT_TIMESTAMP, -> `TheKey` varchar(200) not null, -> `Version` varchar(200), -> primary key(`TheKey`) -> )ENGINE=InnoDB; Query OK, 0 rows affected (0.45 sec) Inserting records into ... Read More

Whenever we renumber, there might be a problem. There is a need to declare a unique ID for a column. In MySQL version 5.6 InnoDB, we can reuse the auto_increment ID by including the ID column in an INSERT statement and we can give any specific value that we want. The situations are as follows − Whenever we delete the ID with the highest number Whenever we start and stop MySQL server Whenever we insert a new record Example of ID auto increment using auto_increment variable. mysql> create table UniqueAutoId -> ( ... Read More

We need to create a stored procedure to display how to pass array to MySQL stored routine. Let us first create a table for our example. Creating a table mysql> create table FindDemo -> ( -> name varchar(100) -> ); Query OK, 0 rows affected (0.46 sec) Inserting some records into the table. mysql> insert into FindDemo values('John'), ('Smith'); Query OK, 2 rows affected (0.13 sec) Records: 2 Duplicates: 0 Warnings: 0 To display all records. mysql> select *from FindDemo; The following is ... Read More

The EXISTS tells if the query returned results or not while IN can be used for comparing one value with other. IN uses literal values. Note − IN is preferred in a subquery whenever a subquery result is very small. If the subquery result is very large then EXISTS is used. Since IN is a preferred choice for subquery results that are smaller, we are considering an example of IN. To create a table. mysql> create table InSubQueryDemo -> ( -> PNumber int, -> PName varchar(100) -> ... Read More

To list all columns in a table, we can use the SHOW command. Let us first create a table. mysql> create table ColumnsList -> ( -> id int, -> Firstname varchar(200), -> LastName varchar(100), -> Age int, -> Address varchar(300), -> CollegeName varchar(100) -> ); Query OK, 0 rows affected (1.33 sec) Syntax to list all column names. show columns from yourTableName; The following is the output. mysql> show columns from ... Read More

We can convert a string to date with the help of STR_TO_DATE() function. Let us see an example. Creating a table. mysql> create table StringToDateDemo -> ( -> YourDate varchar(100) -> ); Query OK, 0 rows affected (0.49 sec) Inserting records into the table. mysql> insert into StringToDateDemo values('10/27/2018'); Query OK, 1 row affected (0.11 sec) The following is the syntax to convert string to date using the STR_TO_DATE() function. SELECT STR_TO_DATE(yourColumnName, '%m/%d/%Y') from yourTableName; Let us now implement it. ... Read More