TSQL -Get上一个日期来自表Audit的记录 [英] TSQL -Get Previous date Records from table Audit
问题描述
大家好,
我正在使用SQL Server 2005,我想从表审核中选择以前的日期记录。
假设我有数据如
Tablename- tblAudit
姓名日期
姓名1 13/10/2010
Name2 15/10/2010
Name3 15/10/2010
Name4 17/10/2010
Name5 19/10 / 2010
假设我将任何日期传递给SQL查询,如16/10/2013,但它不存在于表中tblaudit那个时候sql查询应该返回上一个日期记录,比如
Name2 15/10/2010
Name3 15/10/2010
我们将非常感谢任何帮助
Hi all,
I am using SQL server 2005, I want to select previous date records from table Audit.
Assume i have data like
Tablename- tblAudit
Name date
Name1 13/10/2010
Name2 15/10/2010
Name3 15/10/2010
Name4 17/10/2010
Name5 19/10/2010
Suppose i passed any date to SQL query like 16/10/2013, but it is not present into the table tblaudit that time sql query should return previous date records like
Name2 15/10/2010
Name3 15/10/2010
Any help would be greatly appreciated
推荐答案
在SP中你可以添加一个如下所示的IF条件 -
In the SP you can add a IF condition like given below -
IF EXISTS(SELECT COUNT(*) FROM tblAudit WHERE CONVERT(VARCHAR,[Date],111)=CONVERT(VARCHAR,@Date,111))>0
BEGIN
SELECT * FROM tblAudit WHERE CONVERT(VARCHAR,[Date],111)=CONVERT(VARCHAR,@Date,111)
END
ELSE
BEGIN
SELECT * FROM tblAudit WHERE CONVERT(VARCHAR,[Date],111)=
(
SELECT TOP 1 CONVERT(VARCHAR,@Date,111) FROM tblAudit
WHERE CONVERT(VARCHAR,[Date],111)<=CONVERT(VARCHAR,@Date,111) ORDER BY [Date] desc
)
END
这篇关于TSQL -Get上一个日期来自表Audit的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!