CODEWITHSUNDEEP
sqlitedate
user2121458
user2121458

Reputation: 143

Sqlite: convert a date to a day number in the month

Here is my question: i've some date in my sqlite database. What I want to do is the following:

SELECT dates FROM Table1 WHERE dates > date('now','-1 month');

The result is:

2013-02-21 12:04:22
2013-02-28 12:04:22
2013-02-01 12:04:22

How can I convert those dates as the following:

2013-02-21 12:04:22 become 2 (day 2 of the month)
2013-02-28 12:04:22 become 9 (day 9 of the month)
2013-03-01 12:04:22 become 10(day 10 of the month)

Thank you in advance

Upvotes: 1

Views: 849

Answers (2)

user2121458
user2121458

Reputation: 143

select cast((julianday(dates) - julianday(date('now','-1 month')) + 1)as int)
from table1
where dates > date('now','-1 month')

to keep only the integer part :) thanks a lot to AnatolyS

Upvotes: 0

AnatolyS
AnatolyS

Reputation: 4319

try 

select julianday(dates) - julianday(date('now','-1 month')) + 1
from table1
where dates > date('now','-1 month')

Upvotes: 3

Related Questions