如何在SQL中查找反向日期之间的记录日期 [英] How to find the record date between reverse date in SQL

问题描述

大家好，

我想知道sql server中的以下日期格式

如何在sql中查找反向日期之间的记录日期

i返回低于代码，但它没有显示记录

Hi all,
I want to know the below date format in sql server
how to find the record date between reverse date in sql
i have return below code but its showing no record

declare @frdate datetime, @todate datetime
select @frdate='2016-04-01', @todate ='2015-12-01'
select billdate from test where @frdate between @frdate and @todate order by billdate asc

但是当我执行下面的代码工作正常，

but when i execute below code its working fine,

declare @frdate datetime, @todate datetime
select @frdate='2015-12-01', @todate ='2016-04-01'
select billdate from test where @frdate between @frdate and @todate order by billdate  asc

请让我知道我哪里弄错了，





提前感谢。



我的尝试：



如何在sql中找到反向日期之间的记录日期

please let me know where i made a mistake,


thanks in advance.

What I have tried:

how to find the record date between reverse date in sql

推荐答案

你找不到带有反向日期的记录，从日期开始应该少于todate总是。



如果你想在反向日期提交记录，那么你应该使用desc命令，如下面的查询：

You can not find records with reverse date, from date should be less then todate always.

if you want todate records in reverse date then you should use "order by desc" like below query:

declare @frdate datetime, @todate datetime
select @frdate='2015-12-01', @todate ='2016-04-01'
select billdate from test where @frdate between @frdate and @todate order by billdate desc

Ashish Nigam

Ashish Nigam

BETWEEN [ ^ ]运算符相当于：

The BETWEEN[^] operator is equivalent to:

SomeDateColumn >= @frdate AND SomeDateColumn <= @todate

因此，上限（ @todate ）必须始终大于或等于下限（ @frdate ）。



如果上限低于下限那么条件就没有价值。



对于硬编码边界，​​它很简单，可以按正确的顺序排列。如果边界是由用户提供的，并且您的UI无法验证上限是否大于或等于下限，那么您将需要在SQL中交换值：

Therefore, it's obvious that the upper-bound (@todate) must ALWAYS be greater than or equal to the lower-bound (@frdate).

If the upper-bound is less than the lower-bound, then there is no value for which the condition be true.

For hard-coded bounds, it's simple enough to put them in the correct order. If the bounds are supplied by the user, and your UI is not capable of validating that the upper-bound is greater than or equal to the lower-bound, then you'll need to swap the values in SQL:

declare @frdate datetime, @todate datetime;
select @frdate='2016-04-01', @todate ='2015-12-01';

If @frdate > @todate 
BEGIN
    DECLARE @temp datetime;
    SELECT @temp = @frdate, @frdate = @todate, @todate = @temp;
END;

select billdate from test where SomeDateColumn between @frdate and @todate order by billdate asc;

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