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: