我的日期以dd / mm / yyyy格式存储在数据库中,并且我只想显示日期等于或大于今天的项目.
我尝试了WHERE date(fixtures.date)> = date(‘Now’),但没有任何结果.
下面是我的查询,任何帮助将不胜感激
SELECT fixtures.ID, fixtures.team_1_ID, fixtures.team_2_ID, fixtures.date, fixtures.time, fixtures.pitch, teams.team_name, teams_1.team_name AS awayTeam, leagues.league_name FROM fixtures INNER JOIN teams ON fixtures.team_1_ID = teams.ID INNER JOIN teams AS teams_1 ON fixtures.team_2_ID = teams_1.ID INNER JOIN teams_vs_leagues ON teams.ID = teams_vs_leagues.team_ID INNER JOIN leagues ON teams_vs_leagues.league_ID = leagues.ID WHERE date(fixtures.date) >= date('Now') ORDER BY fixtures.date DESC
@H_403_11@解决方法:
为什么说日期以dd / mm / yyyy格式存储?根据SQLite docs
1.2 Date and Time Datatype
sqlite does not have a storage class set asIDe for storing dates and/or times. Instead, the built-in Date And Time Functions of sqlite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
基本上,此查询将不起作用
select 1 where '01-08-2016' >= date('Now')
@H_403_11@但这会
select 1 where '2016-08-01'>= date('Now')
@H_403_11@因此,您可以验证您的日期fixtures.date是否格式为“ yyyy-MM-dd”,否则查询将无法进行.另外,请记住使用date(‘Now’,’localtime’)来获取您的本地时间.
如果您的日期格式正确,则可以尝试执行以下 *** 作
SELECT fixtures.IDFROM fixturesWHERE fixtures.date >= date('Now')
@H_403_11@如果您确实得到了结果,则联接不匹配任何行.
有关更多信息,请检查此answer
总结以上是内存溢出为你收集整理的android-在SQLite中选择日期大于或等于今天的位置全部内容,希望文章能够帮你解决android-在SQLite中选择日期大于或等于今天的位置所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)