在Sql Server中获取一年的特定日期

I am trying to get the specific day of a Year.

我想要获得一年中的具体日子。

Here's what I have tried till now:-

这是我到现在为止所尝试的: -

-- Declare few variables
DECLARE @Currentdate AS DATETIME
DECLARE @DueDate AS DATETIME
DECLARE @NewDate AS DATETIME

-- Set the variables properly, just for testing
SET @Currentdate = GETDATE()                
SET @DueDate = DATEADD(MONTH, 2, DATEADD(YEAR, 1, @Currentdate))

-- Check the output
SELECT @Currentdate     -- 2013-09-30 00:00:00.000
SELECT @DueDate         -- 2014-11-30 00:00:00.000

So, I want to get the @NewDate based on the @Currentdate year. For this I tried:-

所以,我想根据@Currentdate年份获得@NewDate。为此,我试过: -

SELECT @NewDate = DATEADD(DAY, DAY(DATEDIFF(day, 1, @DueDate)), DATEADD(MONTH, DATEDIFF(MONTH, 0, @Currentdate), 0))
SELECT @NewDate    -- 2013-09-30 00:00:00.000

But it didn't worked. :(

但它没有奏效。 :(

My expected result is like:

我的预期结果如下:

-- 2013-11-30 00:00:00.000
-- Having the due date month and date same, but the year as current date one.

Any help is appreciated!

任何帮助表示赞赏!

UPDATE

Sorry for all the confusion I have created. My question in simple words is:-

对不起我创造的所有困惑。用简单的话说我的问题是: -

I want to get the a new date variable having the date and the month same as @DueDate variable but the year as given in the @Currentdate variable.

我想得到一个新的日期变量,其日期和月份与@DueDate变量相同,但是@Currentdate变量中给出的年份。

I hope that would clear things up a bit.

我希望这会让事情变得清晰起来。

3 个解决方案

#1

If the question is "given I have a particular datetime value in one variable, can I set another variable to be for the same day and month but in the current year" then the answer would be:

如果问题是“给定我在一个变量中有一个特定的日期时间值,我可以将另一个变量设置为同一天和月但在当前年份”,那么答案将是:

declare @DueDate datetime
declare @NewDate datetime

set @DueDate = '20141130'
--Need to set @NewDate to the same month and day in the current year

set @NewDate = DATEADD(year,
       --Here's how you work out the offset
       DATEPART(year,CURRENT_TIMESTAMP) - DATEPART(year,@DueDate),
    @DueDate)

select @DueDate,@NewDate

I want to get the a new date variable having the date and the month same as @DueDate variable but the year as given in the @Currentdate variable.

我想得到一个新的日期变量,其日期和月份与@DueDate变量相同,但是@Currentdate变量中给出的年份。

Well, that's simply the above query with a single tweak:

好吧,这只是上面的查询,只需一个调整:

set @NewDate = DATEADD(year,
       --Here's how you work out the offset
       DATEPART(year,@Currentdate) - DATEPART(year,@DueDate),
    @DueDate)

#2

Try this instead ?

试试这个呢?

DECLARE @Currentdate AS DATETIME
DECLARE @DueDate AS DATETIME

-- Set the variables properly, just for testing
SET @Currentdate = GETDATE()                
SET @DueDate = DATEADD(MONTH, 2, DATEADD(YEAR, 1, 
             DateAdd(day, datediff(day, 0, @currentDate), 0)))

-- Check the output
SELECT @Currentdate     -- 2013-09-30 18:32:35.310
SELECT @DueDate

Using DateAdd(day, datediff(day, 0, @DateTime), 0) strips off the time portion. You should also check out this SO Question/answer.

使用DateAdd(day,datediff(day,0,@ DateTime),0)剥离时间部分。您还应该查看此SO问题/答案。

#3

Try this one:

试试这个:

CAST(CAST(  -- cast INT to VARCHAR and then to DATE
    YEAR(GETDATE()) * 10000 + MONTH(@DueDate) * 100 + DAY(@DueDate) -- convert current year + @DueDate's month and year parts to YYYYMMDD integer representation
    + CASE  -- change 29th of February to 28th if current year is a non-leap year
        WHEN MONTH(@DueDate) = 2 AND DAY(@DueDate) = 29 AND ((YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 <> 0) OR YEAR(GETDATE()) % 400 = 0) THEN 0
        ELSE -1
    END
AS VARCHAR(8)) AS DATE)

#1