In this article, we will explore some of the commonly used date and time functions in SQL Server that allow us to work with date and time values effectively.

AGE()

The AGE() function is useful in business scenarios where we need to calculate ages, service tenures, or durations. It accepts two TIMESTAMP values and returns an interval as a result. Here is the basic syntax:

SELECT AGE(timestamp, timestamp);

For example, to calculate the age between two dates:

SELECT AGE('2021-01-01', '2018-10-24');

We can also use the AGE() function with only one TIMESTAMP argument, which subtracts from the current date:

SELECT AGE(timestamp '1989-03-09');

CURRENT_DATE

The CURRENT_DATE function returns the current date. It is commonly used as the default value for a column. For example:

CREATE TABLE registration (
    student_id serial PRIMARY KEY,
    student_name varchar(255) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE
);

In the above example, the registration_date column will be automatically populated with the current date when a new record is inserted:

INSERT INTO registration (student_name)
VALUES ('John Doe');

CURRENT_TIME

The CURRENT_TIME function returns the current time along with the local timezone. It can also accept an optional precision argument to specify the decimal places. Here is the syntax:

SELECT CURRENT_TIME;

For example, to get the current time with 2 decimal places:

SELECT CURRENT_TIME(2);

Similar to the CURRENT_DATE function, the CURRENT_TIME function can be used to insert the default value for columns that require tracking the system time for entries.

CURRENT_TIMESTAMP

The CURRENT_TIMESTAMP function returns the current date and time along with the local timezone. It is the preferred function for capturing both date and time together. It also accepts an optional precision argument. Here is the syntax:

SELECT CURRENT_TIMESTAMP;

For example, to get the current timestamp with 2 decimal places:

SELECT CURRENT_TIMESTAMP(2);

Just like the previous functions, the CURRENT_TIMESTAMP function can be used to insert the default value for columns that require tracking the timestamp for entries.

Conclusion

This article provides an overview of some of the date and time functions in SQL Server. We have demonstrated how to use these functions in real-world scenarios. By leveraging these functions, you can effectively work with date and time values in your SQL Server applications.

We hope this article has been helpful in your journey of exploring SQL Server’s date and time functions.