SQL | Date Functions (Set-2)

SQL Date Functions are powerful tools that allow users to manipulate, extract , and format date and time values within SQL databases. These functions simplify handling temporal data, making them indispensable for tasks like calculating intervals, extracting year or month values, and formatting dates for display.

In this article, we will explain these functions in detail, demonstrating their applications through practical examples using a sales table.

What are SQL Date Functions?

SQL Date Functions are built-in functions that help manipulate, extract, and format date and time values in SQL databases. These functions are useful for performing operations such as retrieving the current date, extracting specific parts of a date (e.g., year, month, or day), formatting dates, and calculating differences between dates. They are widely used in data analysis, reporting, and scheduling tasks within a database

Some SQL Date Functions

1. MICROSECOND()

This function retrieves the microsecond portion of a date or datetime value, making it useful for operations requiring precise time measurements.

Query:

SELECT MICROSECOND("2018-07-18 09:12:00.000345");

Output

345

2. MINUTE()

Returns the minute portion of a time or datetime value, which is helpful when analysing or filtering data based on time granularity.

Query:

SELECT MINUTE("2018-07-18 09:12:00");

Output

12

3. MONTH()

Extracts the month from a given date value. It is commonly used in reports to group or filter data by month.

Query:

SELECT MONTH ('2018/07/18')AS MONTH;

Output

7

4. MONTHNAME()

Returns the full name of the month for a given date, which is particularly useful in enhancing report readability.

Query:

SELECT MONTHNAME("2018/07/18");

Output

JULY

5. NOW()

Retrieves the current date and time from the server, often used for timestamping events or logging actions.

Query:

SELECT NOW();

Output

2018-07-18 09:14:32

6. PERIOD_ADD():

Adds a specified number of months to a period, making it useful for date calculations involving monthly intervals.

Query:

SELECT PERIOD_ADD(201803, 6);

Output

201809

7. PERIOD_DIFF()

Calculates the difference in months between two periods, aiding in financial or project duration analysis.

Query:

SELECT PERIOD_DIFF(201810, 201802);

Output

8

8. QUARTER()

Extracts the quarter (1–4) from a date, commonly used for quarterly reporting or grouping data by fiscal periods.

Query:

SELECT QUARTER("2018/07/18");

Output

3

9. SECOND()

Retrieves the second portion of a time value, useful for precision timing or interval measurements.

Syntax:

SELECT SECOND("09:14:00:00032");

Output:

0

10. SEC_TO_TIME()

Converts numeric seconds into a human-readable time format, aiding in scenarios like converting durations to time.

Query:

SELECT SEC_TO_TIME(1);

Output

00:00:01

11. STR_TO_DATE()

Converts a string into a date format as per the specified mask, enabling flexible parsing of date strings

Query:

SELECT STR_TO_DATE("JULY 18 2018", "%M %D %Y");

Output

0018-07-18

12. SUBDATE()

Subtracts a specified interval from a date, often used to calculate past dates or filter older records.

Query:

SELECT SUBDATE("2017-06-15", INTERVAL 10 DAY);

Output

2017-06-05

13. SUBTIME()

It returns a time/date time value after a certain time interval has been subtracted.

Query:

SELECT SUBDATE("2018/07/18", INTERVAL 10 DAY);

Output

2018-07-18 09:15:17.542768

14. SYSDATE()

Similar to NOW(), it retrieves the current date and time but calculates it at the query execution time.

Query:

SELECT SYSDATE();

Output

2018-07-18 09:19:03

15. TIME()

Extracts the time portion from a datetime value, commonly used for operations focused solely on time.

Query:

SELECT TIME("09:16:10");

Output

09:16:10

16. TIME_FORMAT()

It formats the time as specified by a format mask.

Query:

SELECT TIME_FORMAT("09:16:10", "%H %I %S");

Output

09 09 10

17. TIME_TO_SEC()

It converts a time value into numeric seconds.

Query:

SELECT TIME_TO_SEC("09:16:10");

Output

33370

18. TIMEDIFF()

It returns the difference between two time/datetime values.

Query:

SELECT TIMEDIFF("09:16:10", "09:16:04");

Output

00:00:06

19. TIMESTAMP()

It converts an expression to a date time value and if specified adds an optional time interval to the value.

Query:

SELECT TIMESTAMP("2018-07-18", "09:16:10");

Output

2018-07-18 09:16:10

20. TO_DAYS()

Converts a date into numeric days since the base date (0), useful for date arithmetic.

Query:

SELECT TO_DAYS("2018-07-18");

Output

737258

21. WEEK()

Retrieves the week number (0–52) of a date, often used in weekly reporting or scheduling.

Query:

SELECT WEEK("2018-07-18");

Output

28

22. WEEKDAY()

Returns the weekday index (0–6) for a date, useful for grouping or filtering data by specific days.

Query:

SELECT WEEKDAY("2018-07-18");

Output

2

23. WEEKOFYEAR()

Retrieves the ISO week number (1–53) of the year for a date, aiding in global reporting standards.

Query:

SELECT WEEKOFYEAR("2018-07-18");

Output

29

24. YEAR()

Extracts the year portion of a date value, frequently used for grouping or filtering data by year.

Query:

SELECT YEAR("2018-07-18");

Output

2018

25. YEARWEEK()

Returns the year and week combined as a numeric value, aiding in chronological sorting or analysis.

Query:

SELECT YEARWEEK("2018-07-18");

Output

201828

Conclusion

SQL Date Functions offer a variety of ways to handle date and time data, making them essential for database manipulation and query optimization. By using these functions, developers can efficiently manage date-related tasks such as formatting, extracting components, and performing date-based calculations. Mastering these functions is important for building robust, data-driven applications and generating meaningful insights from temporal data.

FAQs

How do `NOW()` and `SYSDATE()` differ in SQL?

Both NOW() and SYSDATE() return the current date and time. However, NOW() reflects the time at the beginning of the query execution, while SYSDATE() captures the actual current time when the function is called during the query execution.

How do DATE_ADD() and SUBDATE() differ in SQL?

DATE_ADD() adds a specified time interval to a date, while SUBDATE() subtracts a specified time interval from a date. Both are useful for adjusting date values based on the required operation.

What is the purpose of the STR_TO_DATE() function?

STR_TO_DATE() converts a string representation of a date into a valid date format in SQL based on a specified format mask, allowing for the parsing of custom date formats into a standard SQL date type

S

Sakshi98

Follow

Improve

Article Tags :

• Databases
• SQL
• SQL-Functions