SQL 从 SQLite 中的 DATETIME 获取月份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/650480/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get month from DATETIME in sqlite
提问by Ilija
I am trying to get extract the month from a DATETIMEfield in SQLite. month(dateField)does not work as well as strftime('%m', dateStart).
我正在尝试从DATETIMESQLite的字段中提取月份。month(dateField)效果不如strftime('%m', dateStart)。
Any ideas?
有任何想法吗？
回答by MarmouCorp
I don't understand, the response is in your question :
我不明白，答案在你的问题中：
select strftime('%m', dateField) as Month ...
回答by Jhonny D. Cano -Leftware-
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
如果您正在搜索月份名称，则 SQLite 的核心发行版似乎不支持文本月份名称
回答by Mickey Mazarick
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.
我猜你想把月份作为一个字符串。看起来不是最友好的，但你可能明白这一点。只需用您的变量替换 date('now') 即可。
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
回答by Mar?al
Try using the following:
尝试使用以下方法：
select strftime('%m', datetime(datefield, 'unixepoch')) as month from table
回答by Ferd
I guess strftime('%m', dateStart)is not working for you because dateStart variable is date/datetime type.
我猜strftime('%m', dateStart)这对您不起作用，因为 dateStart 变量是日期/日期时间类型。
Then you must use:
然后你必须使用：
strftime('%m', date(dateStart))
回答by Sten Raadschelders
This is a solution without all the case when statements. Hopefully it's helpful.
这是一个没有所有 case when 语句的解决方案。希望它有帮助。
select substr('JanFebMarAprMayJunJulAugSepOctNovDec', 1 + 3*strftime('%m', date('now')), -3)
回答by Gopinath S
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())
