Hi have one temp #Dates table which is populated by using following condition.

Hi有一个temp #日期表，它使用以下条件填充。

IF @Period = 'Monthly'
    BEGIN
        INSERT #Dates
        SELECT MAX(DATE) WeekDays FROM TABLENAV  WITH (NOLOCK, READUNCOMMITTED) 
        GROUP BY DATEDIFF(MONTH, 0, DATE)
        ORDER BY MAX(DATE) DESC
    END

now data is in #Dates temp table is

现在数据在#Dates temp表中

DATE
2013-04-22 00:00:00.000
2013-03-29 00:00:00.000
2013-02-28 00:00:00.000
2013-01-31 00:00:00.000
2012-12-31 00:00:00.000
2012-11-30 00:00:00.000

I want to get the Previous_Date when I'm passing any Todays_Date

我想在今天通过任何日期时获得之前的日期

I tried like following

我试着像后

DECLARE @Todays_Date datetime = '2013-04-22'
    select date from #Dates where MONTH(DATE) = MONTH(@Todays_Date)-1  AND YEAR(DATE) = YEAR(@Todays_Date)

giving ans : 2013-03-29

给答:2013-03-29

But when Year changes , I'm getting nothing as ans

但是当年份变了，我什么都得不到

For example If @Todays_Date = '2012-01-31' it should return me 2012-12-31 but giving nothing.

例如，如果@Todays_Date = '2012-01-31'，它应该返回2012-12-31，但不提供任何东西。

Help me to modify my select statement.

帮助我修改select语句。

2 个解决方案

select max([Date]) FROM #Date WHERE [Date] <@Todays_Date

SELECT top 1 Date FROM #Date WHERE Date <@Todays_Date order by date desc