SQLite DATE Function

In SQLite database management the DATE() function is useful for handling date and time data very effectively. This function fasts the extraction of the date part from a datetime expression or conversion of a text representation of a date in date format.

In this article, we will learn about the DATE() Function to understand along with its syntax, various examples, and so on.

Understanding the SQLite DATE () Function

At its core, the DATE() function in SQLite serves to extract the date part from a datetime expression or convert a text representation of a date into a valid date format. It accepts a single argument, typically a datetime expression, and returns the corresponding date portion.

Syntax:

DATE(datetime_expression, modifier)

Explanation: datetime_expression: A valid datetime expression, which could be a column name, a datetime literal, or a function returning a datetime value.

modifier: A modifier that specifies the format in which the date should be returned and can be ‘start of month‘, ‘weekday‘, ‘unixepoch‘, ‘utc‘, ‘localtime‘, ‘localtime‘, ‘localtime‘, ‘localtime’ or ‘NNN days’.

Examples of Using the DATE() Function

To delve deeper into the functionality of the DATE() function, let’s consider a hypothetical scenario involving a sample table named transactions. This table stores transaction records, including timestamps, in the following schema.

CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    description TEXT,
    amount REAL,
    transaction_date TIMESTAMP
);

INSERT INTO transactions (description, amount, transaction_date) VALUES
('Groceries', 50.00, '2024-02-08 09:15:00'),
('Gasoline', 35.00, '2024-02-07 17:30:00'),
('Dinner', 75.00, '2024-02-06 20:00:00'),
('Movie Tickets', 40.00, '2024-02-05 18:45:00');

Output:

OUTPUT

Example 1: Extract Date From Datetime Expression

With the help of DATE() function let’s extract the date part from the transaction_date column without get time in the transactions table.

SELECT DATE(transaction_date) AS transaction_date FROM transactions;

Output:

OUTPUT

Explanation: We get the date from the transaction_date from transactions table.

Example 2: Convert Text Representation to Date

The DATE() function is also used to convert a text representation of a date into a correct date format. Suppose we have insert the new transaction record with a date as a text string.

INSERT INTO transactions (description, amount, transaction_date) 
VALUES ('Electronics', 300.00, DATE('2024-02-09'));

Output:

After inserting above data

Explanation: We have successfully convert the Text to Date.

Example 3: Using Date Functions with DATE() Function

Combining the DATE() function with other date functions allows for more intricate operations. For instance, calculating the current date and extracting only the date part:

SELECT DATE('now') AS current_date;

Output:

OUTPUT

Explanation: We have get the current date of system using DATE() Function.

Time String Format

DATE() function is used with datetime expressions which can be represented in various formats. The time string format refers to the specific structure used to represent time-related data within datetime expressions.

Common time string formats defined below in the table:

Format	Description	Example	Notes
‘HH:MM’	Hours and minutes in 24-hour format	’14:30′	It Represents time with precision to minutes.
‘HH:MM:SS’	Hours, minutes, and seconds in 24-hour format	14:30:00′	It Represents time with precision to seconds.
‘HH:MM:SS.SSS’	Hours, minutes, seconds, and milliseconds	’14:30:00.000′	It Represents time with precision to milliseconds.
‘now’	Current date and time	‘2024-02-08 14:30:00’	It Returns the timestamp when the query is executed.
DDDDDDDDDD	Number of days since 0000-01-01	737825	It Represents the days elapsed since the reference.
‘YYYY-MM-DD HH:MM’	Date and time in ISO format with minutes	‘2024-02-08 14:30’	It Represents date and time with minute precision.
‘YYYY-MM-DD’	Date in year-month-day format	‘2024-02-08’	It Represents date in year, month, and day format.
‘YYYY-MM’	Date in year-month format	‘2024-02’	It Represents date in year and month format.

These time string formats enable precise representation and manipulation of time-related data within SQLite queries

Conclusion

The DATE() function is useful for handling date and time data in database applications. Whether extracting the date part from datetime expressions, converting text representations to proper date formats or performing more complex date operations the DATE() function fast data manipulation operations effectively. By understanding the syntax, applications and modifiers of the DATE() function you can enhance your database management capabilities.