如何在SQL Server中获取相同日期之间的记录

When I define a "from" and "to" date to be the same value, the query is not returning any rows. When I select an extra day for the "to" date, then rows are returned.

当我将“from”和“to”日期定义为相同的值时，查询不会返回任何行。当我为“到”日期选择一个额外的日期时，将返回行。

string FrmDt = frmtx.Text.Trim();// 9/1/2015
string ToDt = totx.Text.Trim();//9/1/2015

select 
    rn.*,
    (select count(reue) as RequtNo 
     from tblests 
     left join aspne u on r.custorid = u.userid 
     left join aspnethip a on  u.Used=a.UserId
     where 
         u.refere = rn.refid 
         and r.Compy = 'bbb' 
         and create >= '9/1/2015'
         and create <= '9/1/2015') as refcount 
from 
    tbl_reference rn

Datatype: [Createte] [datime] NOT NULL

数据类型:[Createte] [datime]不为空

How to convert 9/1/2015 12:00:00 AM to 9/1/2015 11:59:59 PM ?

如何将2015年9月1日上午12点至2015年9月1日上午11时59分59秒?

4 个解决方案

#1

You can simply use it like this:

你可以这样简单地使用它:

 createdate >=  '9/1/2015 00:00:00'and createdate <=  '9/1/2015 23:59:59'

or else you can switch to the next day and remove the = from <=

否则，您可以切换到第二天并从<=中删除=

createdate >=  '9/1/2015'and createdate <  '9/2/2015'

To add time to your date you can try like this:

为了增加你的约会时间，你可以这样尝试:

DateTime frmDt = new DateTime(2015, 09, 01); //time is 00:00:00 by default
DateTime toDt = new DateTime(2015, 09, 01);
TimeSpan toDtTime = TimeSpan.Parse("23:59:59");
DateTime toDtFinal = toDt.Add(ToDtTime);

#2

Since your createdate field is a datetime and you define your date stating the date only, the time defaults to 00:00:01. You have three options to deal with that:

由于您的createdate字段是一个datetime，并且您定义了日期，只说明日期，所以默认为00:00:01。你有三个选择:

Set the range to 9/1/2015 - 9/2/2015, which would select whole 24 hours of 9/1/2015

设区间为2015年9月1日- 2015年9月2日，选择2015年9月1日全天24小时
Set the time explicitly to like 00:00:01 and 23:59:59 respectively like 'YYYY-MM-DD HH:MM:SS' (see https://dev.mysql.com/doc/refman/5.7/en/datetime.html for examples).

将时间显式设置为:分别像“YYYY-MM-DD:MM:SS”(参见https://dev.mysql.com/doc/refman/5.7/en/datetime.html示例)。
Do not define a range at all. Simply search for entries from a specific day like this:

根本不要定义范围。简单地搜索某一天的条目如下:

...WHERE DATE(createdate) = '2015-09-01' ...

…其中日期(createdate) = '2015-09-01'…

BTW: in MySQL you define dates as YYYY-MM-DD, so your '9/1/2015' should be '2015-09-01' in fact.

顺便说一句:在MySQL中，你把日期定义为yyyyy - mm - dd，所以你的“9/ 2015”应该是“2015-09-01”。

#3

You can also use this way:

你也可以这样使用:

declare @tempDate datetime = '2015-09-01' declare @endDate datetime = DATEADD(SECOND, -1, DATEADD(DAY, 1, @tempDate))

声明@tempDate datetime = '2015-09-01' declare @endDate datetime = DATEADD(SECOND， -1, DATEADD(DAY, 1， @tempDate))

Here @tempDate os your current date. Now how @endDate is calculated, just add 1 day to @tempDate, then subtract 1 second from that, so you will get the today's date with max time. Now you can simply write query to get date between these two dates.

这里@tempDate显示您当前的日期。现在如何计算@endDate，只需将1天加到@tempDate上，然后减去1秒，你就会得到今天的日期和最大时间。现在，您可以简单地编写查询以获取这两个日期之间的日期。

#4

This works fine. Replace the hard coded date to the date column

这是很好。将硬编码的日期替换为date列

string FrmDt = frmtx.Text.Trim();// 9/1/2015
string ToDt = totx.Text.Trim();//9/1/2015

select 
    rn.*,
    (select count(reue) as RequtNo 
     from tblests 
     left join aspne u on r.custorid = u.userid 
     left join aspnethip a on  u.Used=a.UserId
     where 
         u.refere = rn.refid 
         and r.Compy = 'bbb' 
        AND (YEAR('9/1/2015')=2015 AND MONTH('9/1/2015')= 9 AND DAY('9/1/2015')=1)
         --and create >= '9/1/2015'
         --and create <= '9/1/2015') as refcount 
from 
    tbl_reference rn

#1