在sqlite中更改日期格式 [英] change date format in sqlite

问题描述

将此日期格式更改为sqlite db 12/10/11至12-10-11（mm-dd-yy）我无法执行此操作。我是sqlite中的菜鸟并且必须从报告解析此值 SELECT strftime（'％d-％m-％Y'，Date）但是我得到null作为sqlite db除了 mm-dd-yy 中的值，所以如何将格式 12/10/11转换为12-10-11（mm-dd- yy）。提前谢谢。真的很感谢帮助。

Change this date format which is in sqlite db 12/10/11 to 12-10-11 (mm-dd-yy) I am unable to do so .I am a noob in sqlite and have to parse this value SELECT strftime('%d-%m-%Y',Date) from report but I am getting null as sqlite db excepts value in mm-dd-yy so How do I convert format 12/10/11 to 12-10-11 (mm-dd-yy) .Thanks in advance .Really appreciate the help.

推荐答案

答案简短：

如果你有一个文本字符串存储为12/10/11你想报告为12-10-11，你应该使用 替换（X，Y，Z） 函数，以替换 Y 的出现次数在 X 中， Z 。因此：

If you have a text string stored as "12/10/11" that you want reported as "12-10-11", you should use the replace(X,Y,Z) function, to replace occurrences of Y in X with Z. Thus:

SELECT replace('12/24/11','/','-');

将返回：

12-10-11

---

答案很长：

首先，日期实际上并不存在于SQLite中作为正确的数据类型。它们存储为TEXT，REAL或INTEGER值。请参阅SQLite中的日期和时间数据类型。所以这取决于你的日期在数据库中的存储方式。

First, dates do not actually exist as a proper datatype in SQLite. They're stored as either TEXT, REAL, or INTEGER values. See date and time datatype in SQLite. So it depends upon how your date was stored in the database.

其次，您似乎暗示您将日期存储为 mm / dd / yy 格式。这不是一个有效/有用的 TEXT 格式来存储日期/时间值（因为日期无法排序，不能用于大于和小于操作，不能在SQLite日期函数等中使用）。您确实希望以日期和时间的时间字符串部分中列出的格式之一存储日期时间值函数文档。

Second, you seem to be implying that you stored the date in a "mm/dd/yy" format. That's not a valid/useful TEXT format to be storing date/time values (as the date cannot be sorted, cannot used in "greater than" and "less than" operations, cannot be used in SQLite date functions, etc.). You really want to store datetime values in one of the formats listed in the "Time strings" section of the date and time functions document.

因此，通常您应该将日期/时间值存储为其中一种格式，使用 NSDateFormatter 从数据库中检索时将其转换为 NSDate 。当您想在应用程序中显示日期值时，请使用您想要输出的任何格式。

So, generally you should store your date/time values in one of those formats, use NSDateFormatter to convert that to a NSDate when you retrieve it from the database. And when you want to display the date value in your app, use whatever format you want for output.

但是，如果您不关心日期存储为文本字符串并不能有效地用作SQLite中的日期，然后只需将其视为普通的旧 TEXT 字符串并使用 TEXT 函数，例如替换（X，Y，Z），用<$ c替换/的出现次数$ c> - ，如上所述。

But, if you don't care that the dates are stored as text strings and are not effectively usable as dates in SQLite, then just treat it as a plain old TEXT string and use TEXT functions, such as replace(X,Y,Z) to replace occurrences of "/" with "-", as outlined above.

这篇关于在sqlite中更改日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！