Fixed date with variable year (SQL Server)

Question

I'm trying to set a fixed date every year for last year, this year & next year, eg 1st November.

I can declare my years

 declare @lastyear  int;    set @lastyear = year (getdate()-365) 
 declare @thisyear  int;    set @thisyear = year (getdate()) 
 declare @nextyear  int;    set @nextyear = year (getdate()+365)

But am struggling to see how to set a fixed date each year.

I want to declare the fixed day and month eg

declare @fixeddate; set @fixeddate = CONVERT(VARCHAR(6), '2014.11.01', 107)

and then append the year but am getting nowhere.

something like:

@nextyear + @fixeddate (as a date!)

Help ;-)

Answer 1

Try something like this:

DECLARE @fixedDate VARCHAR(4) = '1101'
DECLARE @addYears INT = 0 -- Add (+)/(-) values to get future and past years
DECLARE @currentYear VARCHAR(4) = CONVERT(VARCHAR(4), Year(Getdate()) + @addYears)

DECLARE @date DATE = CONVERT(DATE, @currentYear + @fixedDate)

If you need to include time as well

DECLARE @fixedDate VARCHAR(13) = '1101 23:59:59' --Change the length as VARCHAR(13)
DECLARE @addYears INT = 0 -- Add (+)/(-) values to get future and past years
DECLARE @currentYear VARCHAR(4) = CONVERT(VARCHAR(4), Year(Getdate()) + @addYears)

--Declare @date as DATETIME and function as CONVERT(DATETIME, ...)
DECLARE @date DATETIME = CONVERT(DATETIME, @currentYear + @fixedDate)

Answer 2

DECLARE @FixedDate DATE = '2014-11-01';
DECLARE @NextYearFixed DATE = DATEADD(year, 1, @FixedDate) -- 2015-11-01
DECLARE @PreviousYearFixed DATE = DATEADD(year, -1, @FixedDate) -- 2013-11-01

Answer 3

You can use DATEADD/DATEDIFF pairs to do this sort of work:

select
  DATEADD(year,DATEDIFF(year,'20010101',GETDATE()),'20011101') as NovThisYear,
  DATEADD(year,DATEDIFF(year,'20010101',GETDATE()),'20001101') as NovLastYear,
  DATEADD(year,DATEDIFF(year,'20010101',GETDATE()),'20021101') as NovNextYear

You just use two dates that have a constant relationship between them that exhibits the final relationship you want (e.g. in the second pair above, I pick the 1st November ('20001101') of the year before the first date ('20010101'))

Results:

NovThisYear             NovLastYear             NovNextYear
----------------------- ----------------------- -----------------------
2014-11-01 00:00:00.000 2013-11-01 00:00:00.000 2015-11-01 00:00:00.000

Answer 4

You can do it like this:

DECLARE
    @lastyear  INT
    ;    
SET 
    @lastyear = DATEPART(YEAR, GETDATE())
    ;
DECLARE 
    @thisyear INT
    ;    
SET
    @thisyear = @lastyear + 1
    ;
DECLARE 
    @nextyear INT
    ;    
SET
    @nextyear = @thisyear + 1
    ;

DECLARE
    @fixeddate DATE
    ; 
SET
    @fixeddate = CAST(CAST(@lastyear AS VARCHAR(4)) + '-' + CAST(11 AS VARCHAR(2)) + '-' + CAST(1 AS VARCHAR(2)) AS DATE)
    ;
SELECT
    @fixeddate
    ;

Answer 5

Thanks All!

I ended up using a combination of all your suggestions to Declare Last, This and Next financial years (FY). Ours is around 1st November but you could use any date!

You just need to replace @today with getdate() ad then it runs forever! You can test it by adding a manual date as int he example below...

The one ares I am stuck with is that it looks like the last date is eg '2014.10.31 00:00:00' instead of '2014.10.31 23:59:59'

You can select it out using DATEADD (S, 86399, @lastFYend) but I need to ensure the @fixedend includes Hours minutes and seconds.

Any suggestions?

-- when are we now?
Declare @today smalldatetime; set @today = '2014.10.14'   -- for testing, normally set to getdate()

Declare @1NOVthisyear   AS smalldatetime;  Set @1NOVthisyear    = DATEADD(year,DATEDIFF(year,'20010101',@today),'20011101') 
Declare @30Octlastyear  AS smalldatetime;  Set @30Octlastyear   = DATEADD(year,DATEDIFF(year,'20010101',@today),'20001031') 

Declare @addyears INT; Set @addyears = (Select Case when @today >= @1NOVthisyear then 1 when @today < @1NOVthisyear then 0 END )

-- FY start / End dates
DECLARE @fixedstart     VARCHAR(4) = '1101'
DECLARE @fixedend       VARCHAR(4) = '1031'

DECLARE @nextnextYear   VARCHAR(4) = CONVERT(VARCHAR(4), Year(@today)   + @addYears +1)
DECLARE @nextYear       VARCHAR(4) = CONVERT(VARCHAR(4), Year(@today)   + @addYears)
DECLARE @thisYear       VARCHAR(4) = CONVERT(VARCHAR(4), Year(@today)   + (@addYears -1))
DECLARE @lastYear       VARCHAR(4) = CONVERT(VARCHAR(4), Year(@today)   + (@addYears -2))
--starts
DECLARE @nextFYstart    DATEtime = CONVERT(smalldatetime, @nextYear     + @fixedstart)
DECLARE @thisFYstart    DATEtime = CONVERT(smalldatetime, @thisYear     + @fixedstart)
DECLARE @lastFYstart    DATEtime = CONVERT(smalldatetime, @lastYear     + @fixedstart)
--ends
DECLARE @nextFYend      DATEtime = CONVERT(smalldatetime, @nextnextYear + @fixedend)
DECLARE @thisFYend      DATEtime = CONVERT(smalldatetime, @nextYear     + @fixedend)
DECLARE @lastFYend      DATEtime = CONVERT(smalldatetime, @thisYear     + @fixedend)

Select

---- testing
--@lastFYstart                      As 'Last FY start', 
--DATEADD (S, 86399, @lastFYend)        AS 'Last FY End',   
--@thisFYstart                      As 'This FY start',
--DATEADD (S, 86399, @thisFYend)        AS 'This FY End',
--@nextFYstart                      As 'Next FY start',
--DATEADD (S, 86399, @nextFYend)        AS 'Next FY End'