MySQL NOW()

In SQL, NOW() function returns the system date and time. This function is very useful to record the date and time of insertion or update of data. This allows you to know exactly when data was added to a table.
 

Syntax:

NOW() function is very easy to use in SQL. You just have to use a query with a syntax like the one below:

SELECT NOW();

This query will return the date and time. A possible result is therefore the following:

2022-09-22 15:20:23

The date and time are displayed in the format YYYY-MM-DD HH:MM:SS (Y=Year, M=Month, D=Day, H=Time, M=Minute, S=Second).
 

 

Example : Add a record with the current date

Let’s assume that any application has a database to store information. This database contains the table of users with their id, name, email, and the date the user was added. This date is very useful to know exactly the day the user was added.
 
User Table:

+----------+---------+--------+-------------------+
|  UserID  |  Name   |  age   | registration_date |
+----------+---------+--------+-------------------+
|    101   |  Alex   |   25   | 2022-09-12        |
|    102   |  Emily  |   15   | 2022-01-12        |
|    103   |  Jean   |   35   | 2022-01-22        |
|    104   |  Bob    |   40   | 2022-02-01        |
+----------+---------+--------+-------------------+

Let’s say we add a user on September 12, 2022. The SQL query to add this user will look like this:

INSERT INTO users ( Name, age, registration_date ) 
VALUES ( 'Nicolas', 25, NOW() );

Output:

+----------+---------+--------+-------------------+
|  UserID  |  Name   |  age   | registration_date |
+----------+---------+--------+-------------------+
|    101   |  Alex   |   25   | 2022-09-12        |
|    102   |  Emily  |   15   | 2022-01-12        |
|    103   |  Jean   |   35   | 2022-01-22        |
|    104   |  Bob    |   40   | 2022-02-01        |
|    105   | Nicolas |   25   | 2022-09-12        |
+----------+---------+--------+-------------------+

This result shows that the date and time will be added, without having to determine them via a programming language. It is therefore possible to keep a simple SQL query to add users.
 

 

Get the current date:

It is easily possible to get the current date from the function NOW(). To do so, you just have to use the DATE() or CAST() function.
 

Extract the date with DATE() function:

DATE() function is used to get a date from a string in DATE or DATETIME format. Just use the following syntax to get the current date.

SELECT DATE( NOW() );

Output:

2022-10-30

 

 

Extract the date with CAST() function:

The CAST() function allows converting a type into another type. It is therefore possible to convert DATETIME format into DATE type.

SELECT CAST(NOW() AS DATE);

Output:

2022-10-30

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More