在sqlite中用字符串dd-mm-yyyy格式比较日期 [英] comparison dates with string dd-mm-yyyy format in sqlite

问题描述

我将我的日期存储为dd-mm-yyyy"格式的字符串.现在我想做一个这样的比较:

SELECT strftime('%d/%m/%Y', myDate) as myDate从我的表WHERE myDate>='01/07/2013' 和 myDate<='24/07/2013'

但我什么也没得到.这个查询有什么问题?.

这不是重复问题.我将所有内容存储为字符串，而不是日期或日期时间.我试图在日期格式的字符串之间进行比较.因此，否决票是不公平的.

解决方案

您无法通过当前设置使查询像这样工作:

<块引用>

我将日期存储为字符串，所以我想通过使用 strftime 我可以获得我想要的.还是我错了?

基本上:你错了！

这里的问题是这样格式化的字符串将无法按照您想要的方式进行排序.

特别是:

myDate>='01/07/2013' and myDate<='24/07/2013'

此处，任何一年中任何一个月的第一天到第二十四天之间的任何日期都将与此匹配.即.这将匹配:02/01/1900"，这将匹配:02/12/2099".

这样做的原因是字符串比较是字母数字，而不是数字(或按日期).02"大于01"小于24"，剩下的只是多余的.

将日期格式化为字符串的逻辑"方法是从最重要的值开始，然后向下工作，即.这种格式:yyyy/mm/dd"，而不是相反，但是存储日期的真正逻辑方式是将其存储为日期，而不是字符串.

处理字符串的唯一方法是将每个字符串转换为日期并进行处理，但最佳方法是修复您的架构.>

如果你绝对不能改变你当前的架构，这里有一种方法可以让它工作，但我不建议这样做:

 where (substr(myDate, 7, 4) || '-' || substr(myDate, 4, 2) || '-' || substr(myDate, 1, 2)) '2013-07-01' 和 '2013-07-24'

在进行比较之前，这会将字符串分开，并以正确的顺序将它们重新组合在一起.还要注意最后两个日期的格式.

Im storing my dates as string in format 'dd-mm-yyyy'. Now I want to do a comparison like this:

SELECT strftime('%d/%m/%Y', myDate) as myDate
FROM myTable
WHERE myDate>='01/07/2013' and myDate<='24/07/2013'

But I get nothing. Whats wrong with this query?.

THIS IS NOT A DUPLICATE QUESTION. I was storing everything as String, no as a DATE or DATETIME. I was trying to do a comparison between strings with date format. So is not fair the down vote.

解决方案

There is no way you can get your query to work like that with your current setup:

I'm storing the date as a String so I guess with the using of strftime I can get what I want. Or am I wrong?

Basically: You're wrong!

The problem here is that a string formatted like that will not be sortable in the way you want.

Specifically:

myDate>='01/07/2013' and myDate<='24/07/2013'

Here, any date that is between the first and the 24th of any month in any year will match this. Ie. this will match: "02/01/1900", as will this: "02/12/2099".

The reason for this is that string comparisons are alphanumerical, not numerical (or datewise). "02" is greater than "01" and less than "24", and the rest is just redundant.

The "logical" way to format a date as a string is to start with the most significant value and work your way downwards, ie. this format: "yyyy/mm/dd", and not the other way around, but the real logical way to store a date, is to store it as a date, and not as a string.

The only way to work with the strings is to convert each string to a date and work with that, but the best way is to fix your schema.

If you absolutely cannot change your current schema, here is a way to make it work, but I do not advice this:

where (substr(myDate, 7, 4) || '-' || substr(myDate, 4, 2) || '-' || substr(myDate, 1, 2)) between '2013-07-01' and '2013-07-24'

This will pick apart the strings, put them together again in the right order, before doing the comparison. Also note the format of the last two dates there.

这篇关于在sqlite中用字符串dd-mm-yyyy格式比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！