How to get current date time format in SQlite?

Question

I am using sqlite for local database in mobile and in my database. i want to know that

How to get current date format in SQLITE? I want to get date in the next format: MM/dd/yyyy

Answer 1

To get the current date you can use:

SELECT date('now'); 

Note: This is NOT a server date, it's the same time you get if you query the date and time directly from your application because SQLITE runs in-process.

It's mostly useful for putting a current time into a table or for some simple calculations if your language's date processing is very poor.

To do the calculations see the SQLITE Documentation

See the docs for formatting too for example:

SELECT strftime('%Y-%m-%d %H:%M:%S', datetime('now')) 

Answer 2

According to the SQLite documentation as of this writing (3/30/2020), in the section titled "The DEFAULT clause", it is recommended that a constant value be used instead of a sub-query.

In my experimentation, I also ran into issues with the SQLite CREATE TABLE statement that was generated during model creation by EF Core 3.0 when using SELECT datetime('now'). The SQLite data provider complained of a syntax error when using a SELECT statement within the CREATE table statement. As such, I would recommend using the CURRENT_TIMESTAMP keyword.