英文:
SQL Oracle table blank when trying to query date data stored as varchar
问题
我有一个名为received_dt_key的varchar格式的列，格式为DD-MM-YYYY（例如，30-07-2021）。
我想要从表中选择所有日期在2021年12月31日和2022年1月1日之间的数据。我尝试了下面查询的不同版本，但输出是一个空表。
SELECT *
FROM SD_BDAY
WHERE to_char(to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY')) > to_char(to_date('31-12-2021', 'DD-MM-YYYY'))
and to_char(to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY')) < to_char(to_date('01-01-2022', 'DD-MM-YYYY'));
英文:
I have a column called received_dt_key in Varchar in the format DD-MM-YYYY (e.g. 30-07-2021).
I would like to select all from the table for dates between 31-12-2021 and 01-01-2022. I have tried version of the below query and a blank table is the output.
SELECT *
FROM SD_BDAY
WHERE to_char(to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY')) > to_char(to_date('31-12-2021', 'DD-MM-YYYY'))
and to_char(to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY')) < to_char(to_date('01-01-2022', 'DD-MM-YYYY'));
答案1
得分: 1
不要将日期视为字符串进行比较。请将它们视为日期进行比较：
SELECT *
FROM SD_BDAY
WHERE to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') > to_date('31-12-2021', 'DD-MM-YYYY')
and to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') < to_date('01-01-2022', 'DD-MM-YYYY');
如果尝试将它们作为字符串进行比较，那么您实际上是在寻找大于'31-12-2021'且小于'01-01-2022'的字符串，字符串比较将查看第一个字符并查看是否可以找到一个匹配项，而大于'3'且小于'0'的匹配项是永远不可能的，因此，当作为字符串进行比较时，不会返回任何结果。
正如@AlexPoole在评论中指出的那样，即使您将值作为日期进行比较（而不是字符串），您仍不会返回结果，因为查找大于DATE '2021-12-31'且小于DATE '2022-01-01'的值将返回所有日期，从2021-12-31 00:00:01到2021-12-31 23:59:59；然而，您的值将始终转换为午夜时间组件，因此永远不会落入该范围，因此无法返回。
您可能想要使用>=而不是>，这样将匹配2021-12-31的值。
英文:
Don't compare dates as strings. Compare them as dates:
SELECT *
FROM SD_BDAY
WHERE to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') > to_date('31-12-2021', 'DD-MM-YYYY')
and to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') < to_date('01-01-2022', 'DD-MM-YYYY');
If you try to compare them as strings then you are looking for string that is greater than '31-12-2021' and less than '01-01-2022' and the string comparison will look at the first character and see if it can find a match which is greater than '3' and less than '0'; there can never be such a match so it is quite correct that when comparing as strings nothing is returned.
As pointed out by @AlexPoole in comments, even if you compare the values as dates (rather than strings) you will still never return a result as finding values that are greater than DATE '2021-12-31' and less than DATE '2022-01-01' would return all dates from 2021-12-31 00:00:01 to 2021-12-31 23:59:59; however, your values will always be converted with a midnight time component and, therefore, will never fall into that range so cannot be returned.
What you probably want is to use >= rather than > and then it would match values on 2021-12-31.
答案2
得分: 0
最好的方法是将日历日期存储在日期数据类型的列中。你认为 Oracle 为什么设计了这种数据类型？这样，你可以在日期数据类型列上创建普通索引，或者如果需要的话，可以根据该日期列对表进行分区。
不过，如果你坚持要以这种方式存储日历日期，我认为下面的方法应该可以工作：
SELECT *
FROM SD_BDAY
WHERE to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') >
to_date('31-12-2021', 'DD-MM-YYYY')
and to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') <
to_date('01-01-2022', 'DD-MM-YYYY');
这样，你比较的是日历日期与日历日期，而不是字符与字符，正如你所编写的代码所产生的结果。
如果 varchar2 列中包含无法转换为日期的内容会怎样呢？这就是为什么最好使用日期数据类型的原因。
英文:
The best thing would be to store calendar dates in date data type column. Why else do you think Oracle designed that data type? This way you may create normal indexes on data data type columns, or, if needed, partition the table by that date column.
Still, if you insist in having the calendar dates stored like that, I think the below should work:
SELECT *
FROM SD_BDAY
WHERE to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') >
to_date('31-12-2021', 'DD-MM-YYYY')
and to_date(RECEIVED_DT_KEY, 'DD-MM-YYYY') <
to_date('01-01-2022', 'DD-MM-YYYY');
Thus you compare calandar dates with calendar dates, not varchar with varchar, as it results from the code you have written.
And what if in the varchar2 column there is somethibng that can't be converted to date? That is why it is best to use the date data type.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论