38

I am trying to get extract the month from a DATETIME field in SQLite. month(dateField) does not work as well as strftime('%m', dateStart).

Any ideas?

Prasen
  • 217
  • 1
  • 5
  • 15
Ilija
  • 659
  • 2
  • 7
  • 13

10 Answers10

53

I don't understand, the response is in your question : 

select strftime('%m', dateField) as Month ...
MarmouCorp
  • 1,573
  • 10
  • 9
24
SELECT strftime('%m', datefield) FROM table

If you are searching the month name, text month names does not seems to be supported by the core distribution of SQLite

Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116
Jhonny D. Cano -Leftware-
  • 17,663
  • 14
  • 81
  • 103
  • 3
    this is not correct if datefield is a native datetime type. You need to use strftime('%m', datetime(datefield, 'unixepoch')) as mentioned in another answer. – Zig Mandel Jul 13 '16 at 15:33
14

I'm guessing you want to get the month as a string. Not the most friendly looking but you probably see the point. Just replace date('now') with your variable.

select case strftime('%m', date('now')) 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' else '' end
as month
Mickey Mazarick
  • 141
  • 1
  • 2
13

Try using the following:

select strftime('%m', datetime(datefield, 'unixepoch')) as month from table
bfavaretto
  • 71,580
  • 16
  • 111
  • 150
Marçal
  • 131
  • 1
  • 3
4

This is a solution without all the case when statements. Hopefully it's helpful. 

select substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3*strftime('%m', date('now')), -3)
Sten Raadschelders
  • 41
  • 2
2

I guess strftime('%m', dateStart) is not working for you because dateStart variable is date/datetime type.

Then you must use: 

strftime('%m', date(dateStart))
Ferd
  • 1,273
  • 14
  • 16
1

After all those years I think I figured out what was your problem. You probably store your dates as strings. This way when you search for data from let's say May you should write sql like this:

Select * from table where strftime('%m', datefield) = '05'

It's important to write '05' and not '5'. Using '5' will give you zero results.

zakkary
  • 11
  • 1
0

Here is my solution that worked for me 

MONTH_DICT={ "Jan" : '01', "Feb" : '02', "Mar" : '03', "Apr" : '04', "May" : '05', "Jun" : '06', "Jul" : '07', "Aug" : '08', "Sep" : '09', "Oct" : 10, "Nov" : 11, "Dec" : 12 }

self.cursor.execute("SELECT * FROM error_log WHERE strftime('%m',Date_column)=?",(MONTH_DICT[query_month],)) 

print('output:', self.cursor.fetchall())
Gopinath S
  • 511
  • 6
  • 20
0

SQLLite does not support monthnames, you need to use substr(Date, 4, 2) as month to get the months

%sql select substr(date,4,2) as month from ;

KingBobushcka
  • 1
-4

SELECT strftime('%m',datetime_col) as "Month" FROM table_name;

just replace 'datetime_col' with your datetime column

sunilk
  • 11
  • 1