按日期和日期获取数据顺序应该从sql中的当前日期开始。 [英] Get data order by date and date should be start from current date in sql.
问题描述
按日期和日期获取数据顺序应从sql中的当前日期开始
我有一些记录,如
思想日期
A 01 Jan
B 02 Jan
C 03 Jan
.... ...... ..
X 12月
现在我要输出 - 当前日期(2016年9月18日)
思想日期
P 18 Sep
Q 19 Sep
R 20 Sep
.... ...... ..
X 31 Dec
A 01 Jan
B 01 Jan
...... .. .........
Y 01 Sep
... .. ... ...
Z 9月17日
我尝试过:
SELECT ThoughtId,CompanyCode,CompanyId,Thought,
convert(varchar(6),ThoughtDate,106)ThoughtDate
,(isnull时的情况( IsActive,0)= 0然后'InActive'其他'活动'结束)作为状态,
CreatedBy,CreatedDate,ModifyBy,ModifyDate
FROM ThoughtofTheDay
WHERE CompanyId = 214
ORDER BY ABS(DATEDIFF(day,ThoughtDate,GETDATE()))asc
思想ThoughtDate1状态
18-Sep活跃
CA 17-Sep活跃
B 19-Sep活跃
B 20-Sep有效
p 16-Sep Active
i 15-Sep Active
f 21-Sep Active
j 22-Sep Active
在上面的输出中,我希望过去的日期将在未来的日期出现
喜欢9月17日,9月16日,15 sep应该在2017年(2017年6月16日......)
如果我理解你的问题,你需要添加条件以仅从当前日期开始获取想法并更改排序。类似
SELECT ThoughtId,
CompanyCode,
CompanyId,
思想,
转换(varchar(6),ThoughtDate, 106)ThoughtDate,
情况
当isnull(IsActive,0)= 0然后'InActive'
其他'有效'
以状态结束,
CreatedBy,
CreatedDate,
ModifyBy,
ModifyDate
FROM ThoughtofTheDay
WHERE CompanyId = 214
AND ThoughtDate> = GETDATE()
ORDER BY thoughDate ASC
如果我理解正确的话......
你需要以这种方式修改ORDER BY子句:
ORDER BY
CASE WHEN ThoughtDate> = @passdate AN D ThoughtDate< DAYADD(DD, 3 , @passdate )那么 ThoughtDate END ASC ,
CASE WHEN ThoughtDate< @passdate 那么 ThoughtDate END DESC
欲了解更多详情,请参阅:
SQL Server&#8211; ORDER BY子句中的自定义排序| Sql And Me [ ^ ]
ORDER BY子句中的SQL SERVER-CASE语句 - 使用变量的ORDER BY - 使用Pinal Dave的SQL权限之旅 [ ^ ]
按案例排序|使用Manoj的SQL [ ^ ]
get data order by date and date should be start from current date in sql
I have some record like Thought Date A 01 Jan B 02 Jan C 03 Jan …. ….. X 31 Dec Now I Want To Output like – Current Date(18 Sep 2016) Thought Date P 18 Sep Q 19 Sep R 20 Sep …. …….. X 31 Dec A 01 Jan B 01 Jan ….. ……… Y 01 Sep ….. …… Z 17 Sep
What I have tried:
SELECT ThoughtId, CompanyCode, CompanyId, Thought, convert(varchar(6),ThoughtDate,106) ThoughtDate ,(case when isnull(IsActive,0)=0 then 'InActive' else 'Active' end)as Status, CreatedBy, CreatedDate, ModifyBy, ModifyDate FROM ThoughtofTheDay WHERE CompanyId=214 ORDER BY ABS(DATEDIFF(day, ThoughtDate, GETDATE()))asc
Thought ThoughtDate1 Status
A 18-Sep Active
CA 17-Sep Active
B 19-Sep Active
B 20-Sep Active
p 16-Sep Active
i 15-Sep Active
f 21-Sep Active
j 22-Sep Active
In above output I want that date which has passed will come in future date
like 17 Sep,16 Sep,15 sep should come in 2017(16 sep 2017....)
If I understand your question correctly, you need to add a condition to fetch only thoughts starting from current date and change the sorting. Something like
SELECT ThoughtId, CompanyCode, CompanyId, Thought, convert(varchar(6),ThoughtDate,106) ThoughtDate, case when isnull(IsActive,0)=0 then 'InActive' else 'Active' end as Status, CreatedBy, CreatedDate, ModifyBy, ModifyDate FROM ThoughtofTheDay WHERE CompanyId=214 AND ThoughtDate >= GETDATE() ORDER BY ThoughDate ASC
If i understand you correctly...
You need to modify ORDER BY clause this way:
ORDER BY CASE WHEN ThoughtDate >= @passeddate AND ThoughtDate < DAYADD(DD, 3, @passeddate) THEN ThoughtDate END ASC, CASE WHEN ThoughtDate < @passeddate THEN ThoughtDate END DESC
For further details, please see:
SQL Server – Custom sorting in ORDER BY clause | Sql And Me[^]
SQL SERVER - CASE Statement in ORDER BY Clause - ORDER BY using Variable - Journey to SQL Authority with Pinal Dave[^]
ORDER BY CASE | SQL with Manoj[^]
这篇关于按日期和日期获取数据顺序应该从sql中的当前日期开始。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!