2

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:-

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.

I hope that would clear things up a bit.

palaѕн
  • 72,112
  • 17
  • 116
  • 136

3 Answers3

3

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.

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)
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

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.

Community
  • 1
  • 1
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
  • Thanks for your time and answer but the issue here is not the time portion its the date which i need like `2013-11-30` not `2013-09-30` instead! I have updated the question to clear some confusion too. – palaѕн Sep 30 '13 at 13:50
  • You are explicitly adding two months to the current date's month value by doing `DateAdd(month, 2 ...` If you don't want to do that, just take it out - and the code in my answer DOES generate 30 November, not 30 September. – Charles Bretana Sep 30 '13 at 14:24
  • i.e., the only difference between what you post as the "expected answer" and the "actual answer", is the time portion. – Charles Bretana Sep 30 '13 at 14:28
0

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)
miazo
  • 144
  • 7