CODEWITHSUNDEEP
sqlite
Usman Farooq
Usman Farooq

Reputation: 109

How to deal with date in SQLite?

I have a table in Sqlite DB having two fields Id and Date (Date is of type Text). I have stored a few dates in the table from c#. now i want to get the records matching specific day, month and year. The query i have tried is:

select strftime('%m', Date) from testTbl Where id = 3;

also:

select Date(substr(Date, 0, 10))  as daa from testTbl Where id = 3;

but the result of these two quires is always null.. can anyone help me to sort this out?

Upvotes: 0

Views: 409

Answers (1)

user6996876
user6996876

Reputation:

Proposed (immediate) fix

Use the following select

select substr(Date, 0, 10)  as daa from testTbl Where id = 3;

Cause of the issue

The problem (if you surround the above substr with a Date function) is that you're using a Text type that is not in the expected format

Time Strings

A time string can be in any of the following formats:

YYYY-MM-DD

YYYY-MM-DD HH:MM

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS.SSS

YYYY-MM-DDTHH:MM

YYYY-MM-DDTHH:MM:SS

YYYY-MM-DDTHH:MM:SS.SSS

HH:MM

HH:MM:SS

HH:MM:SS.SSS

now

DDDDDDDDDD

Alternative (better) approach

Anyway IMHO, it would be better to create the column with a Date type and to insert values in the following way

insert into testTbl values (DateTime("2015-12-31"),3);

so that you'll be able to do

SELECT strftime('%m/%d/%Y',Date) from testTbl where id = 3;

or also

SELECT Date from testTbl where Date > DateTime('2016-01-01');

from C# the parameterized command would be similar to

"insert into testTbl values (DateTime(?),?);"

with a parameter value myDate.ToString("yyyy-MM-dd")

Anyway you can actually get the month string with substr(Date,0,2) and the year with substr(Date,5,4) with your current format. I'm simply suggesting an alternative that I would find more standard (using the built-in Date format)

Upvotes: 1

Related Questions