CODEWITHSUNDEEP
sqlite
NobodyMan
NobodyMan

Reputation: 2441

How to create a datetime column with default value in sqlite3?

Is there a way to create a table in sqlite3 that has a datetime column that defaults to 'now'?

The following statement returns a syntax error:

create table tbl1(id int primary key, dt datetime default datetime('now'));

Update: Here's the correct ddl courtesy of Sky Sanders:

create table tbl1(id int primary key, dt datetime default current_timestamp);

Upvotes: 67

Views: 117927

Answers (4)

Mahendranatarajan
Mahendranatarajan

Reputation: 57

you can use the following query for using current date value in your table

create table tablename (date_field_name Created_on default CURRENT_DATE);

Upvotes: 0

kev
kev

Reputation: 161634

CURRENT_TIMESTAMP is a literal-value just like 'mystring'

column-constraint:

enter image description here

literal-value:

enter image description here

Upvotes: 9

Sky Sanders
Sky Sanders

Reputation: 37074

Try this:

create table tbl1(id int primary key, dt datetime default current_timestamp);

Background:

The DEFAULT constraint specifies a default value to use when doing an INSERT. The value may be NULL, a string constant, a number, or a constant expression enclosed in parentheses. The default value may also be one of the special case-independant keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. If the value is NULL, a string constant or number, it is inserted into the column whenever an INSERT statement that does not specify a value for the column is executed. If the value is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the current UTC date and/or time is inserted into the columns. For CURRENT_TIME, the format is HH:MM:SS. For CURRENT_DATE, YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

From http://www.sqlite.org/lang_createtable.html

Upvotes: 109

Doug Currie
Doug Currie

Reputation: 41170

... default (datetime(current_timestamp))

The expression following default must be in parentheses. This form is useful if you want to perform date arithmetic using SQLite date and time functions or modifiers.

Upvotes: 18

Related Questions