{"id":3024,"date":"2024-04-12T21:04:20","date_gmt":"2024-04-12T14:04:20","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=3024"},"modified":"2024-04-12T21:50:02","modified_gmt":"2024-04-12T14:50:02","slug":"sqlite-current_date","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-date-functions\/sqlite-current_date\/","title":{"rendered":"SQLite current_date Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQLite current_date<\/code> function to return the current date in UTC.<\/p>\n\n\n\n

Introduction to the SQLite current_date function<\/h2>\n\n\n\n

In SQLite, the current_date<\/code> function returns the current date in UTC.<\/p>\n\n\n\n

The following shows the syntax of the current_date<\/code> function:<\/p>\n\n\n

current_date<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The current_date<\/code> function returns the current UTC date in the format YYYY-MM-DD<\/code>.<\/p>\n\n\n\n
In practice, you can use the current_date<\/code> function as a default value for a date column or insert\/update the current date into a date column.<\/p>\n\n\n\n
SQLite current_date function examples<\/h2>\n\n\n\n
Let’s take some examples of using the current_date<\/code> function.<\/p>\n\n\n\n
1) Basic SQLite current_date function example<\/h3>\n\n\n\n
The following example shows how to use the current_date<\/code> function to retrieve the current date:<\/p>\n\n\n
SELECT<\/span> current_date<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
current_date\n------------\n2024-04-12<\/code><\/span><\/pre>\n\n\n
To get the current date in the local time, you can pass the current_date to the date() function as follows:<\/p>\n\n\n
SELECT<\/span> date<\/span>(current_date<\/span>, 'localtime'<\/span>) local_date;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
local_date\n----------\n2024-04-12<\/code><\/span><\/pre>\n\n\n
2) Using the current_date function to calculate ages<\/h3>\n\n\n\n
First, create a new table<\/a> called members<\/code> to store member data:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> members(\n    id<\/span> INTEGER<\/span> PRIMARY KEY<\/span>,\n    name<\/span> VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>,\n    date_of_birth TEXT<\/span> NOT<\/span> NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> members(name<\/span>, date_of_birth) \nVALUES<\/span>\n    ('John Doe'<\/span>, '1999-06-17'<\/span>),\n    ('Jane Smith'<\/span>, '1998-09-01'<\/span>),\n    ('Alice Boutique'<\/span>, '1998-11-10'<\/span>)\nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id | name           | date_of_birth\n---+----------------+--------------\n1  | John Doe       | 1999-06-17\n2  | Jane Smith     | 1998-09-01\n3  | Alice Boutique | 1998-11-10\n\n(3 rows)<\/code><\/span><\/pre>\n\n\n
Second, calculate the age of members using the current_date<\/code> function:<\/p>\n\n\n
SELECT<\/span> \n  name<\/span>, \n  date_of_birth, \n  current_date<\/span> as<\/span> today,\n  CAST<\/span>(((julianday(current_date<\/span>) - julianday(date_of_birth)) \/ 365.25<\/span>) AS<\/span> INTEGER<\/span>) age\nFROM<\/span> \n  members \nORDER<\/span> BY<\/span> \n  name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
name           | date_of_birth | today      | age\n---------------+---------------+-----------+-----\nAlice Boutique | 1998-11-10    | 2024-04-12 | 25\nJane Smith     | 1998-09-01    | 2024-04-12 | 25\nJohn Doe       | 1999-06-17    | 2024-04-12 | 24<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using the current_date function as the default value of a column<\/h3>\n\n\n\n
We’ll take an example of using the current_date<\/code> function as the default value of a date column.<\/p>\n\n\n\n
First, create a table<\/a> called purchase_requests<\/code> to store the purchase requests:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> purchase_requests(\n  id<\/span> INTEGER<\/span> PRIMARY KEY<\/span>, \n  product_name TEXT<\/span> NOT<\/span> NULL<\/span>, \n  quantity INT<\/span> NOT<\/span> NULL<\/span>,\n  requested_date DATE<\/span> DEFAULT<\/span> current_date<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In the purchase_requests<\/code> table, the requested_date<\/code> column uses the current_date<\/code> as the default value.<\/p>\n\n\n\n
INSERT<\/span> INTO<\/span> purchase_requests(product_name, quantity) \nVALUES<\/span> ('Laptop'<\/span>, 20<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The INSERT<\/code> statement does not have a requested date, therefore, it uses the current date in UTC as the default value.<\/p>\n\n\n\n
Third, retrieve the data from purchase_requests<\/code> table:<\/p>\n\n\n
SELECT<\/span> * FROM<\/span> purchase_requests;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
id | product_name | quantity | requested_date\n---+--------------+----------+---------------\n1  | Laptop       | 20       | 2024-04-12<\/code><\/span><\/pre>\n\n\n
The output shows that the INSERT<\/code> statement inserted the current date into the requested_date<\/code> column.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the current_date<\/code> function to retrieve the current date in UTC.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert a new row<\/a> into the purchase_requests<\/code> table:<\/p>\n\n\n
Second, insert rows<\/a> into the members<\/code> table:<\/p>\n\n\n