[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Calculate the contractual year period

Hi

I would be grateful if someone could create (or point me in the direction of) a user defined function that takes 4 parameters  (1) fromDD  (2) fromMM  (3) toDD  (4) toMM and dependant on todays date will return two dates (1) fromDate (2) toDate

Example 1:

fromDD = 01
fromMM = 04
toDD = 31
toMM = 03
todays date 18th July 2006

Return values would be

fromDate = 1-Apr-2006
toDate = 31-Mar-2007


Example 2:

fromDD = 01
fromMM = 10
toDD = 30
toMM = 09
todays date 18th July 2006

Return values would be

fromDate = 1-Oct-2005
toDate = 30-Sep-2006


Thanks.

0
cubixSoftware
Asked:
cubixSoftware
  • 2
1 Solution
 
Mr_PeerapolCommented:
DECLARE @fromDD CHAR(2)
DECLARE @fromMM CHAR(2)
DECLARE @toDD CHAR(2)
DECLARE @toMM CHAR(2)

DECLARE @curYear INT
SET @curYear = YEAR(GETDATE())

DECLARE @fromDate DATETIME
DECLARE @toDate DATETIME

SET @fromDate = CONVERT(DATETIME, CONVERT(VARCHAR, @curYear)+@fromMM+@fromDD, 112)
SET @toDate = CONVERT(DATETIME, CONVERT(VARCHAR, @curYear)+@toMM+@toDD, 112)

IF (@fromDate > @toDate)
  BEGIN
    SET @toDate = DATEADD(y, 1, @toDate)
  END

-- Here you may return the date as VARCHAR by using CONVERT(VARCHAR, @toDate, XXX). Consult bookonline for XXX to get the format you want

/*
You may need to create 2 user-defined functions - one for getFromDate, the other for getToDate.
GETDATE() cannot be used inside a user-defined fucntion, so you need to pass it to your functions as well.
*/
0
 
jrb1Commented:
CREATE PROCEDURE getFromToDates
( @fromDD varchar, @fromMM varchar, @toDD varchar, @toMM varchar
, @fromDate datetime OUT, @toDate datetime OUT)
AS
BEGIN
   @fromDate = @fromMM + "/" + @fromDD + "/" + Year(GetDate())
   if @fromDate > GetDate()
      @fromDate = DateAdd(YEAR, -1, @fromDate)

  @toDate = @toMM + "/" + @toDD + "/" + Year(GetDate())
  if @toDate < GetDate()
     @toDate = DateAdd(YEAR, 1, @toDaet)
END
0
 
jrb1Commented:
sorry....last toDaet needs to be toDate
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now