在SQL中根据日期过滤数据 [英] Filter data based on date in sql

问题描述

我正在尝试执行以下SQL查询并根据日期过滤出数据.

Im trying to execute the following SQL query and filter out the data based on the date.

我需要显示一个表，该表过滤出数据，以便仅显示提到的起始日期和结束日期之间的那些行

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

这是我一直在尝试的查询

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

我也得到了2012年的结果.请帮帮我

I get the result with values from 2012 as well. Please help me out

谢谢

推荐答案

由于您没有提到任何错误，因此如果START_DATE和END_DATE是DATETIME数据类型，则您的查询没有任何问题.如果您没有获得正确的记录，请检查数据.

Since you have not mentioned about any errors, if START_DATE and END_DATE are DATETIME data type, there is nothing wrong with your query. If you are not getting the correct records, Please check the data.

但是您的date format may trouble you in a different server.您可以遵循一些好的做法来避免此类问题.

However your date format may trouble you in a different server. There are some good practices you could adhere to avoid such issues.

-每当将日期用作字符串时，请尝试在 ISO或ISO8601 中使用它 格式(即'yyyymmdd'或'yyyy-mm-ddThh:mi:ss.mmm')

-Whenever date is used as a string, try to use it in ISO or ISO8601 format (ie 'yyyymmdd' or 'yyyy-mm-ddThh:mi:ss.mmm')

-还要避免使用旧的 WHERE Table1，Table2 联接表 过时的. JOIN的执行效果更好，整洁.

-Also avoid joining tables with WHERE Table1, Table2 which is old and obsolete. JOINs are much better performed, neat and tidy.

您可以按以下方式更改查询；

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

这篇关于在SQL中根据日期过滤数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！