Get 1st April

• dramaqueen

  SSCommitted

  Points: 1569

  More actions

  April 21, 2016 at 6:40 am

  #325754

  Hey,

  I need to find a nice neat way of setting a variable to 1st April of that current 'contract year' based on today's date.

  Today's Date Contract Start Date

  2016-04-21 > 2016-04-01

  2016-01-12 > 2015-04-01

  2018-05-01 > 2018-04-01

  2016-01-01 > 2015-04-01 (my script give me 2016-04-01)

  I want to use GETDATE() so that it can be ran automatically.

  DECLARE @STARTDATE_CURRENT VARCHAR (20)

  SET @STARTDATE_CURRENT = '2016-01-01'

  DECLARE @CONTRACT_STARTDATE VARCHAR (20)

  SET @CONTRACT_STARTDATE = CASE

  WHEN Substring(@STARTDATE_CURRENT, 5, 2) IN ( '01',

  '02', '03' ) THEN Cast(

  Year(Cast(Substring(@STARTDATE_CURRENT, 1, 4)AS DATE

  ))-1 AS VARCHAR)

  + '0401'

  ELSE Cast(Year(Cast(Substring(@STARTDATE_CURRENT, 1,

  4)AS DATE)) AS VARCHAR)

  + '0401'

  END

  PRINT @CONTRACT_STARTDATE

  Thanks in advance for any help.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 21, 2016 at 6:45 am

  #1873588

  Try this

  DECLARE @CurrentDate DATETIME = GETDATE() --'2016-01-01'

  select DATEADD(qq,1,DATEADD(yy,DATEDIFF(yy,0,DATEADD(qq,-1,@CurrentDate)),0))

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• dramaqueen

  SSCommitted

  Points: 1569

  More actions

  April 21, 2016 at 6:52 am

  #1873595

  Thank you very much GilaMonster! It worked perfectly.