在MS SQL中获取两个日期之间的记录 [英] Get Records between two dates in MS SQL

问题描述

我有一个表格，其中我有一个名为dtCreated的列，其数据类型为VARCHAR（50）



现在我想要两个日期之间的记录。



我写的查询如下所示

I have a table in which i have a column named "dtCreated" which has got datatype as VARCHAR(50)

Now I want records between two dates.

I wrote my query as written below

<br />
SELECT * FROM mytable WHERE dtcreated>=fromdate AND dtcreated<=todate<br />

当fromdate和ToDate是同月时，此查询正常工作。即如果fromDate = 1/1/2011

和todate是1/31/2011它将正常工作



但是如果fromdate是1 / 1/2011和todate是2/1/2011然后将不会返回任何记录。



请给我解决方案

This query work fine when fromdate and ToDate are of same month. i.e if fromDate =1/1/2011
and todate is 1/31/2011 it will work fine

But if fromdate is 1/1/2011 and todate is 2/1/2011 then no records will be returned.

Please give me solution of it

推荐答案

虽然Umair的答案可能有用，但不能保证让你想要你想要的。



我说可能是因为：你为什么使用 VARCHAR（50）保留日期而不是 DateTime ？在您的应用程序中，所有这些可能都是相同的，但不是在您运行查询时。

While Umair's Answer might work, it is not guaranteed to get you want you want.

I say might because: why are you using VARCHAR(50) to hold dates instead of DateTime? In your app all these might be the same, but not when you run your query.

01/01/2011<br />
Jan-01-2011<br />
JAN 01, 2011<br />
2011/01/01<br />
January 01, 2011<br />
01-JAN-11<br />

因为您使用的是 VARCHAR（50）它们都是存储在您的字段中的有效数据。是否可以将字段类型更改为 DateTime ？如果是这样的话，你就会减轻头痛和压力。



只是我的2 <小> cts

Since your are using VARCHAR(50) they all valid data to store in your field. Is it possible to change your field type to DateTime? if so, you will have less headache and maintence.

just my 2cts

这个将为你工作：

This will work for you:

SELECT * FROM mytable WHERE CONVERT(DATETIME,dtcreated)>=fromdate AND CONVERT(DATETIME, dtcreated)<=todate

希望这个回答你的问题

Hope this answer your question

这是一个在sql中为数据使用适当的数据类型的好习惯。



如果您的数据是日期值。那么你可以使用'datetime'数据类型或'smalldatetime'。这也有助于避免将来出现数据完整性问题。 coz，sql不允许在您的数据中使用非日期值。



如果您已将数据类型转换为'datetime'或'smalldatetime'。你可以有这个简单的查询：

It's a good practice to use appropriate data type for your data in sql.

if your data is a date value. then you can use 'datetime' data type or 'smalldatetime'. This will also help you avoid future problems with data integrity. coz, sql will not allow non-date value in your data.

if you have converted your data type to 'datetime' or 'smalldatetime'. you can have this simple query:

SELECT * FROM mytable WHERE dtcreated BETWEEN fromdate AND todate

这篇关于在MS SQL中获取两个日期之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！