SQL sql中根据日期过滤数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14397569/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filter data based on date in sql
提问by shockwave
Im trying to execute the following SQL query and filter out the data based on the date.
我试图执行以下 SQL 查询并根据日期过滤掉数据。
I need to display a table which filters out the data such that, only those rows which are between the mentioned start_date and end_date
我需要显示一个过滤掉数据的表，只有那些在提到的 start_date 和 end_date 之间的行
Here's the query that I have been trying
这是我一直在尝试的查询
SELECT DISTINCT T1.column1, T1.column2, T2.START_DATE, T2.END_DATE
FROM Table1 T1, Table2 T2
WHERE (T1.column1= T2.column2) AND
(T2.START_DATE >= '15/01/2013 10:58:58' AND
T2.END_DATE <= '18/01/2013 10:58:58') ORDER BY T2.START_DATE DESC
I get the result with values from 2012 as well. Please help me out
我也得到了 2012 年的结果。请帮帮我
Thanks
谢谢
回答by Kaf
Since you have not mentioned about any errors, if START_DATEand END_DATEare DATETIMEdata type, there is nothing wrong with your query. If you are not getting the correct records, Please check the data.
由于您没有提到任何错误，如果START_DATE和END_DATE是DATETIME数据类型，那么您的查询没有任何问题。如果您没有得到正确的记录，请检查数据。
However your date format may trouble you in a different server. There are some good practices you could adhere to avoid such issues.
然而你的date format may trouble you in a different server. 您可以遵循一些好的做法来避免此类问题。
-Whenever date is used as a string, try to use it in ISO or ISO8601format (ie 'yyyymmdd'or 'yyyy-mm-ddThh:mi:ss.mmm')
-Also avoid joining tables with WHERE Table1, Table2which is old and obsolete. JOINs are much better performed, neat and tidy.
- 无论何时将日期用作字符串，请尝试以ISO 或 ISO8601格式使用它（即'yyyymmdd'或'yyyy-mm-ddThh:mi:ss.mmm'）
- 还要避免将表与旧的和过时的WHERE Table1、Table2连接起来。JOIN 执行得更好，整洁。
You can change your query as follows;
您可以按如下方式更改查询；
SELECT DISTINCT T1.column1, T1.column2, T2.START_DATE, T2.END_DATE
FROM Table1 T1 JOIN Table2 T2 ON T1.column1 = T2.column2
WHERE (T2.START_DATE >= '20130115 10:58:58' AND
T2.END_DATE <= '20130118 10:58:58')
ORDER BY T2.START_DATE DESC
