YTD Date calculation

Question

YTD Date calculation

jagjitsingh

Ten Centuries

Points: 1059
More actions
August 17, 2021 at 6:18 am

#3920619
Hi
I have @frdate parameter. In below condition i want to Set date as '01/04' and subtract 1 year from @frdate
if Month(@frDate) < 4
begin
SET @ytdfrDate = "01/04/" + dateadd(y,-1,@frdate)
end
Thanks
- This topic was modified 3 years, 3 months ago by jagjitsingh.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

ktflash Default port Points: 1400 More actions · Answer 1

String Concat missing, ur trying to concat a string and an integer

DECLARE @frdate date = '01-01-2021'DECLARE 
@ytdfrDate date
if Month(@frDate) < 4begin
SET @ytdfrDate = '01/04/' + cast(dateadd(y,-1,@frdate) as nvarchar(4))
end
select @ytdfrDate

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

ktflash wrote:

String Concat missing, ur trying to concat a string and an integer

DECLARE @frdate date = '01-01-2021'DECLARE 
@ytdfrDate date
if Month(@frDate) < 4begin
SET @ytdfrDate = '01/04/' + cast(dateadd(y,-1,@frdate) as nvarchar(4))
end
select @ytdfrDate

Bit of an odd solution, this. Your literal date is not in ISO format and why use NVARCHAR()?

DECLARE @FRDate DATE = '20210228';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - 1, 4, 1);

SELECT @FRDate
      ,@newFRDate;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ktflash Default port Points: 1400 More actions · Answer 3

Phil Parkin wrote:

ktflash wrote:
String Concat missing, ur trying to concat a string and an integer
DECLARE @frdate date = '01-01-2021'DECLARE 
@ytdfrDate date
if Month(@frDate) < 4begin
SET @ytdfrDate = '01/04/' + cast(dateadd(y,-1,@frdate) as nvarchar(4))
end
select @ytdfrDate
Bit of an odd solution, this. Your literal date is not in ISO format and why use NVARCHAR()?
DECLARE @FRDate DATE = '20210228';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - 1, 4, 1);

SELECT @FRDate
      ,@newFRDate;

Did not even know DATEFROMPARTS is a thing, good to know.

Thanks i guess

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Phil Parkin wrote:

DECLARE @FRDate DATE = '20210228';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - 1, 4, 1);

SELECT @FRDate
      ,@newFRDate;

+1000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Jeff Moden wrote:

Phil Parkin wrote:

DECLARE @FRDate DATE = '20210228';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - 1, 4, 1);

SELECT @FRDate
      ,@newFRDate;

+1000

Would agree - except the solution doesn't work for dates where month is greater than 4. I also looked at the date and assumed January 4th, but it appears the OP actually means April 1.

DECLARE @FRDate DATE = '20210501';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - iif(month(@FRDate) < 4, 1, 0), 4, 1);

SELECT @FRDate
      ,@newFRDate;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Would agree - except the solution doesn't work for dates where month is greater than 4

I read the requirement several times and ended up shrugging my shoulders and sending in a best guess, yet you sound as if you are 100% certain of what this means:

i want to Set date as '01/04' and subtract 1 year from @frdate

In my opinion, this does not qualify as a clear and concise description of the requirement.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

If you look at the original post, he checks for month less than 4.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Jeffrey Williams wrote:

If you look at the original post, he checks for month less than 4.

Indeed, but there is no ELSE in there, so we have to make assumptions about what happens if month >= 4, because that scenario is not covered at all in the original post.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

Yes that is correct.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

Jeffrey Williams wrote:

Jeff Moden wrote:
Phil Parkin wrote:
DECLARE @FRDate DATE = '20210228';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - 1, 4, 1);

SELECT @FRDate
      ,@newFRDate;
+1000
Would agree - except the solution doesn't work for dates where month is greater than 4. I also looked at the date and assumed January 4th, but it appears the OP actually means April 1.
DECLARE @FRDate DATE = '20210501';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - iif(month(@FRDate) < 4, 1, 0), 4, 1);

SELECT @FRDate
      ,@newFRDate;

Yeah... sorry. I looked at the original problem the wrong way. I also didn't realize that 01/04 was in the (I believe) dd/mm format although that should have been obvious to me by what Phil posted.

I need to look at it a bit more before I continue my yapping. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

Jeff Moden wrote:

Yeah... sorry. I looked at the original problem the wrong way. I also didn't realize that 01/04 was in the (I believe) dd/mm format although that should have been obvious to me by what Phil posted.
I need to look at it a bit more before I continue my yapping. 🙁

As Phil pointed out - there wasn't an ELSE in his question. I assumed the OP actually wants the first of the fiscal year - and we all know what assuming means 🙂

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

Ok... assuming the following requirements...

Given any DATETIME value, calculate the starting date of the Fiscal Year the value falls into.
The Fiscal year starts on the 1st of April.

In the code below, the CTE generates all dates using a Tally Function (see the last link in my signature line below for the one I use) from the start of the year 1900 up to and and including the start of the year 2100, each having a random time assigned to it, as well.

Then, it uses those sequential dates with random times to demonstrate the a formula to calculate the start of the Fiscal Year it falls into regardless of year, month, day or time of day and strips out the time, as well.

   WITH cteGenDate AS
(
 SELECT SomeDate = DATEADD(dd,t.N,'1900')+RAND(CHECKSUM(NEWID()))
   FROM dbo.fnTally(0,DATEDIFF(dd,'1900','2100'))t
)
 SELECT  SomeDate
        ,FYStartDt = DATEADD(mm,DATEDIFF(mm,0,SomeDate)-(((MONTH(SomeDate)-1)+9)%12),0)
   FROM cteGenDate
;

p.s. I left the -1)+9 thing in the formula just for study purposes for those that might want to use the same formula for a different starting month. For a Fiscal year start of the 1st of April, that could be optimized to just a +8.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101096 More actions · Answer 13

I don't see the need for all that, this is much simpler:

SET @ytdfrDate = DATEFROMPARTS(YEAR(@frdate) - 
    CASE WHEN MONTH(@frdate) < 4 THEN 1 ELSE 0 END, 04, 01)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 14

ScottPletcher wrote:

I don't see the need for all that, this is much simpler:
SET @ytdfrDate = DATEFROMPARTS(YEAR(@frdate) - 
    CASE WHEN MONTH(@frdate) < 4 THEN 1 ELSE 0 END, 04, 01)

Sweet. I first tested it for DATETIME values from 1900 through 2099 and got the correct results. Then I tested it on a million rows and it's 20ms faster than mine (seems trivial to most but that's a win, IMHO). As you say, it's also simpler to understand and, if we replace the CASE with IIF (which resolves to a CASE behind the scenes so no perf change when I measured it), it's really short.

 SELECT FiscalYearStartDT = DATEFROMPARTS(YEAR(@frdate)-IIF(MONTH(@frdate)<4,1,0),4,1);

Nicely done, Scott.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)