Ravindra BagaleCourses & study guides Track your progress

6. SQL Data Types Analysts Actually Meet

6.3 How SQLite Stores Dates and Times (Text, Not a Date Type)

By Last updated:

Why. Almost every report asks about a date or a month. You must know how the date is stored before you filter it.

What. SQLite has no separate date type. Dates are usually stored as TEXT in the ISO format YYYY-MM-DD HH:MM:SS. Because the year comes first, sorting this text also sorts the dates in the right order.

How. Check the type of posted_at with typeof(), and split it into a date and a time with date() and time().

Steps

  1. Run the first example and confirm that posted_at is TEXT.
  2. Read the date part and the time part in the next two columns.
  3. Run the second example to see the latest three transactions sorted by posted_at.
  4. Always store new dates in the YYYY-MM-DD format, so that sorting and filtering stay correct.

Examples

Example 1 · dataset: northstar_bank

SELECT posted_at,
       typeof(posted_at) AS stored_as,
       date(posted_at)   AS just_date,
       time(posted_at)   AS just_time
FROM transactions
WHERE txn_id IN (1, 2, 3);

Output

posted_atstored_asjust_datejust_time
2026-08-03 09:15:00text2026-08-0309:15:00
2026-08-19 14:40:00text2026-08-1914:40:00
2026-08-07 11:05:00text2026-08-0711:05:00

Meaning. posted_at is plain TEXT. The date() and time() functions read that text and return only the date part or only the time part.

Example 2 · dataset: northstar_bank

SELECT txn_id, posted_at
FROM transactions
ORDER BY posted_at DESC
LIMIT 3;

Output

txn_idposted_at
242026-10-31 23:30:00
232026-10-30 14:21:00
222026-10-30 14:20:00

Meaning. The newest transactions come first. Sorting works because ISO text puts the year, then the month, then the day.

Ravindra Bagale's Tip

A date written as 03/09/2026 is not safe in SQLite, because it sorts as text and can mean March or September. Convert such dates to YYYY-MM-DD first.