How to Get the Current Date and Time in SQL

By Community / Developer
Mar 20, 2023

This article was written by Pius Aboyi. Scroll down for the author’s bio.

SQL databases have several functions that reduce the complexity of working with date and time. Using these functions and a date and time type column, you can depend on SQL for the logic to write and read data with date and time.

In this post, you’ll learn how to use the SQL date and time functions to get the current date and time.

What are date and time functions?

SQL date and time functions provide different options that help with extracting, converting, and formatting date and time values.

There are date functions that process dates only, time functions that process time only, and date-time functions that can perform operations on a full date that includes the time.

The date and time functions available to users vary depending on the SQL distribution in use, such as MySQL versus SQL Server. The following tables list some date and time functions for MySQL and SQL Server and describe what they do.

MySQL date and time functions

SQL Server date and time functions

Why use date and time functions?

From providing a standard format for writing and reading date-time values to helpful functions that can convert time values, SQL date and time functions have many uses. Next, we’ll describe just a few of them.

Querying data from a specific date

You can use the date and time functions to query an SQL database for records related to a specific date and time. For example, you can use the CURDATE() function in MySQL to get data with a date field value equal to the current date.

Querying data from past or future date

In addition to querying for a specific date and time, SQL has functions that let you add or subtract from a specific time value to find data related to a date and time before or after a specific time. For instance, you can use SQL date and time functions to fetch data from yesterday, or fetch records of items in your inventory that will expire in a few days’ time.

Converting the date and time format

Querying date and time in SQL can be complex due to inconsistent formatting. However, SQL provides functions that you can use to convert from one date format to another.

How to get the current date and time in SQL

At this point, you know what the SQL date and time functions do. You also understand some of the uses for these functions. Now, let’s walk through some practical examples of how to use the CURDATE() function.

Example 1: Get all records from today

In this example, we’ll use the current date function in MySQL to query a table for all records with a date field value equal to the current day.

First, you need to have records in the table you want to query. For this example, create a goods table and insert the following data into it.

Next, let’s query the table for goods that will expire today if today were February 24, 2023.

To do that, run the following SQL query via your preferred programming language or database viewer.

SELECT * FROM goods WHERE expiry_date  = CURDATE();

For the above dataset, the query will return the following result:

What happened here is that the CURDATE() function returned the value for the current day in the default SQL DATE type format. In this example, CURDATE is 2023-02-24. Hence, the query returns all the goods with expiry_date set to 2023-02-24.

Example 2: Get all records posted at the current hour

In the last example, we demonstrated how to query data with the current date. Let’s now walk through how we can use the current time function.

Again, prepare the dataset you’ll be searching. Create a stories table that contains the following data:

Then, run the following SQL query to fetch stories posted at the current hour, assuming the current time is 5:00 a.m.:

SELECT * FROM stories WHERE DATE_FORMAT(posted_on, '%H') = HOUR(CURTIME());

The WHERE clause in this code snippet looks a little more complex than in the previous example. Now, let’s break things down:

The DATE_FORMAT() function is an SQL date and time function that takes a date-time value as input and returns whatever value you specify (in a valid time format) as output. So, in the case of our example, the value for the post_on column is the input, whereas, ‘%H’ is a format that will return only the value for the hour in posted_on for each row.

Another time function in the query is HOUR(). This function extracts only the value for the hour from CURTIME().

The CURTIME() function returns the current time in hour:minutes:seconds format.

Example 3: Get the Current Date and Time in MS SQL

In this example, we’ll walk through how to get the current date and time using Microsoft SQL Server.

To get the current date and time of the server that your SQL runs on, use the following query:

SELECT GETDATE();

This query returns the current date and time together. For example, running this query on the 25th of December 2022 at 12 pm will return the following result:

2022-12-25 12:00:45

You can combine the GETDATE() function with other date and time functions to perform operations like fetching data from specific days that come before the current date. Or you can do the inverse, and fetch data with a date after the current day.

What is a time series database?

Though SQL date and time functions reduce some of the complexity in working with date and time, there’s still room for improvement. And that’s where a time series database comes in.

A time series database is designed for storing data collected over time, like, for example, logs from a sensor that reads data at different points in time.

A time series database usually has a column for timestamps and it stores the actual data relative to the timestamp. For instance, it can store temperature readings across multiple time points. If we translate this type of data into a graph, one axis would indicate time and the other would indicate temperature.

With a time series database, it’s easier to query the value at a specific point in time from a large record.

Conclusion

To wrap up, let’s recap everything we covered in this post. First, we explained that SQL date and time functions make it easy to work with date and time in SQL.

Then we listed some date and time functions for two popular SQL databases (MySQL and MS SQL Server).

Next, you saw ways you can use date and time functions, with practical examples for getting current date and time in SQL.

Lastly, for uses like logging, you learned that creating a date-time column in an SQL table might not be the best solution. Hence, we gave a small introduction to time series databases, which might be a better alternative than a date-time column, as it binds each record to a timestamp, among other features.

About the author:

This post was written by Pius Aboyi. Pius is a mobile and web developer with over 4 years of experience building for the Android platform. He writes code in Java, Kotlin, and PHP. He loves writing about tech and creating how-to tutorials for developers.