Click here to Skip to main content
15,943,061 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted
Comments
RedDk 12-Oct-13 13:44pm    
SELECT [Name], [date] FROM [tblAudit] WHERE [date] < '16/10/2013'

1 solution

In the SP you can add a IF condition like given below -

SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900