NOW() function in SQL: Definition, syntax and practical examples in Select, Update and Insert queries

Welcome to srcodigofuente.com! In this tutorial I will teach you everything you need to know about the SQL NOW function.

This function is very useful for working with dates and times in databases and is a must in any project that involves transactions or date management.

Content

  • Definition and syntax
  • Examples of using the NOW() function in SELECT queries
    • Example 1: Get the current date and time
    • Example 2: Get the records created in the last 24 hours
    • Example 3: Update the modification date of a record

Definition and syntax

The NOW() function in SQL is used to get the current date and time of the system on which the query is being executed.

It is important to mention that this function varies depending on the database engine used. In MySQL, for example, NOW() is equivalent to CURRENT_TIMESTAMP().

In Oracle, the equivalent function is SYSDATE.

The syntax of the NOW() function is very simple:

NOW()

This function does not require any parameters and is used directly in SQL queries.

If you want to know more about the NOW() function in different database engines, you can consult the following official definitions:

  • Official definition of NOW() in MySQL
  • Official definition of CURRENT_TIMESTAMP() in MySQL
  • Official definition of SYSDATE in Oracle

Examples of using the NOW() function in SELECT queries

Example 1: Get the current date and time

SELECT NOW();

Result:

NOW()
2023-02-24 16:30:45

Example 2: Get the records created in the last 24 hours

Suppose we have a table of products in an online store and we want to get the products that have been added in the last 24 hours.

We can use the NOW() function together with the DATE_SUB() function to get the date and time of 24 hours ago and compare it with the date the records were created.

SELECT * FROM productos
WHERE fecha_creacion >= DATE_SUB(NOW(), INTERVAL 1 DAY);

Result:

id name price Creation date
1 Red shirt 19.99 2023-02-23 15:30:45
3 Blue sweatshirt 49.99 2023-02-24 10:45:32
4 Black pants 29.99 2023-02-24 12:30:15

Example 3: Update the modification date of a record

Suppose we want to update the modification date of a record in our products table.

We can use the NOW() function in the UPDATE query to insert the current date and time.

UPDATE productos
SET fecha_modificacion = NOW()
WHERE id = 1;

Result:

id name price Creation date modification date
1 Red shirt 19.99 2023-02-23 15:30:45 2023-02-24 16:30:45
2 Black jacket 89.99 2023-02-21 12:15:20 2023-02-21 09:30:00
3 Blue sweatshirt 49.99 2023-02-24 10:45:32 2023-02-24 10:45:32

In addition to the NOW function, there are many other useful functions for working with dates in SQL. Here are some examples of what can be done:

  • DATEADD: Allows you to add or subtract a time interval to a given date.
  • DATEDIFF: Calculates the difference between two dates.
  • DATEPART: Returns a specific part of a date, such as the day, month, or year.
  • GETDATE: Returns the current date and time.
  • DATEFORMAT: Formats a date according to a specified pattern.

These functions can be very useful for working with dates in SQL, either for filtering data or for performing calculations.

If you want to learn more about how to work with dates in SQL, we recommend that you investigate more about these functions and try them in your own projects.

We hope you found this tutorial on the SQL NOW function useful and help you understand how it works.

Remember that the best way to learn is by practicing, so feel free to try the examples we have shown here and experiment with your own queries.

Good luck!

Leave a Reply