SQLLite strftime 不读取列值 [英] SQLLite strftime not reading column value

问题描述

我在我的 Sqllite 数据库查询中有这个，我想要......

select BoardingDate, strftime('%Y', date(BoardingDate)) from Hostels;

但在输出中它正确呈现 BoardingDate 和输出

BoardingDate 月份-------------------------8/1/2007 0:00:00 空3/1/2008 0:00:00 空8/1/2008 0:00:00 空2009 年 3 月 1 日 0:00:00 空

这些是查询的当前行为

 1. strftime('%Y', date('now')) - 输出正确2. strftime('%Y', date('2020-03-06')) - 它也适用3. strftime('%Y', date(BoardingDate)) - 给出一个错误，它根本不呈现..4. strftime('%Y', date(Hostels.BoardingDate)) - 同样的错误...5. strftime('%Y', datetime(BoardingDate)) - 给出错误，6. strftime('%Y', datetime(Hostels.BoardingDate)) - 不起作用....

它打印字符串，我不知道为什么.它的工作方式.我正在尝试使用 julianday() 函数计算日期，但这是一种更长的方法我现在很困惑.谢谢.

这是我在消息面板中得到的错误

select BoardingDate, strftime('%Y',BoardingDate)>没有这样的栏:登机日期>时间:0s

但第一列正确渲染，但函数列返回此错误我尝试按照这些页面上的解决方案进行操作，但无济于事

get-month-from-datetime-in-sqlite

sqlite-convert-string-to-date

我已经尝试过本文档中的其他功能lang_datefunc.html但是当我尝试将列引用作为参数传递时，我仍然遇到相同的错误在函数中

解决方案

这个如何在查询字符串中将 8/1/2007 转换为 08/01/2017"没有帮助，因为那不是有效的 sqlite 时间字符串.如果 MySql 不是一个选项(它不适合我)，您将不得不执行复杂的字符串函数".一旦它们被分解，这可能会相当直接.

年份很简单，它是 substr(adate,-12,4) 假设 时间组件只有一位数字表示小时(否则为 -13).如果你真的只想要年份(基于 '%Y')，那么鲍勃就是你的叔叔.

同样，这个月很容易.利用 CAST 将文本转换为整数直到第一个非数字字符，并使用 printf 到 0 pad，这个 printf('%02i',CAST (adate as int)) 将给出月份.

这一天的输入要多一些，因为您需要在第一个/"之后的字符串部分.这个 printf('%02i',CAST (substr(adate,instr(adate,'/') + 1) as int)) 可以解决问题.

剩下的就是用一些 - 将它们连接在一起，瞧，这是一个有效的 sqlite 日期.

i have this in my Sqllite Database query, i want ...

select BoardingDate, strftime('%Y', date(BoardingDate)) from Hostels;

but in the output it renders BoardingDate correctly and the output

BoardingDate        Month 
-------------------------
8/1/2007 0:00:00    null
3/1/2008 0:00:00    null
8/1/2008 0:00:00    null
3/1/2009 0:00:00    null

These is the current behaviour of the queries

 1. strftime('%Y', date('now'))         - outputs correctly
 2. strftime('%Y', date('2020-03-06'))  - it works also
 3. strftime('%Y', date(BoardingDate))  - Gives an error, it doesnt render at all.. 
 4. strftime('%Y', date(Hostels.BoardingDate))  - The same error...
 5. strftime('%Y', datetime(BoardingDate))  - Gives an error,
 6. strftime('%Y', datetime(Hostels.BoardingDate))  - doesnt work.... 

it prints the string, i dont know why. its working this way. i'm trying to calculate the date using the julianday() function but thats a longer approach i'm just confused right now. thanks.

This is the error i get in the Message Panel

select BoardingDate, strftime('%Y',BoardingDate)
> no such column: BoardingDate
> Time: 0s

but the first column render correctly, but the function column returns this error I tried following the solutions on these pages but to no avail

get-month-from-datetime-in-sqlite

sqlite-convert-string-to-date

i have tried other functions from this documentation lang_datefunc.html but i still get the same error, when i try to pass a column reference as a parameter in a function

解决方案

This "how do i convert 8/1/2007 to 08/01/2017 within a query string" won't help, since that is not a valid sqlite time string. And if MySql is not an option (which it is not for me), you would have to do the "complicated string functions". Which can be fairly straight forward once they're broken down.

The year is easy, it is substr(adate,-12,4) assuming the time component only has one digit for hour (-13 otherwise). And if you really only want the year (based on '%Y') then bob's your uncle.

The month, likewise, is easy. Exploiting CAST which will convert text to an integer until the first non-numeric character, and using printf to 0 pad, this printf('%02i',CAST (adate as int)) will give the month.

The day is a little more typing, because you need the part of the string after the first '/'. This printf('%02i',CAST (substr(adate,instr(adate,'/') + 1) as int)) will do the trick.

All that's left is to concat them all together with some - in between, and voilà, it's a valid sqlite date.

这篇关于SQLLite strftime 不读取列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！