CODEWITHSUNDEEP
sqlsqlite
ORStudios
ORStudios

Reputation: 3233

Selecting Unique Months and Years From a SQLITE database

I have a column called (date) in an expenses table that displays a date in the formate "2015-02-15". I want to build a list of the Distinct unique months and years so that the user can pick what month to show. I have tried using the following answer without any luck.

"1" "1" "Food Shopping" "156"   "2015-01-15"
"2" "1" "Water" "200"   "2015-02-15"
"3" "2" "Car Maintenance"   "30"    "2014-12-15"
"4" "4" "Boobs" "200"   "2015-02-15"

So Ideally I should get

December 2014
January 2015
February 2015

Could someone point me in the right direction. Thanks.

Upvotes: 2

Views: 1534

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If you can live with ISO standard formats, you can do:

select distinct date(date, '%Y-%m')
from expenses;

If not, you can use logic to get the month name:

select distinct date(date, '%Y') || ' ' ||
       (case when date(date, '%m') = '01' then 'January'
             . . .
             when date(date, '%m') = '12' then 'December'
        end)

Oops, I put the year first and then the month. You can reverse them if you like.

Upvotes: 1

CL.
CL.

Reputation: 180210

Your date column is already in the correct format, so it is not necessary to use the unixepoch modifier. Just remove it from the original query:

SELECT CASE m 
       WHEN '01' THEN 'January' 
       WHEN '02' THEN 'Febuary' 
       WHEN '03' THEN 'March' 
       WHEN '04' THEN 'April' 
       WHEN '05' THEN 'May' 
       WHEN '06' THEN 'June' 
       WHEN '07' THEN 'July' 
       WHEN '08' THEN 'August' 
       WHEN '09' THEN 'September' 
       WHEN '10' THEN 'October' 
       WHEN '11' THEN 'November' 
       WHEN '12' THEN 'December' 
       END || ' ' || y AS dates
FROM 
(
  SELECT DISTINCT 
         strftime('%m', date) m,
         strftime('%Y', date) y
    FROM expenses
)
ORDER BY y, m

Upvotes: 4

Related Questions