August 17, 2021 at 6:18 am
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
August 17, 2021 at 7:46 am
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
August 17, 2021 at 8:03 am
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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 17, 2021 at 9:02 am
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 @ytdfrDateBit 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
August 17, 2021 at 3:49 pm
DECLARE @FRDate DATE = '20210228';
DECLARE @newFRDate DATE = DATEFROMPARTS(YEAR(@FRDate) - 1, 4, 1);
SELECT @FRDate
,@newFRDate;
+1000
--Jeff Moden
August 17, 2021 at 6:27 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2021 at 6:43 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 17, 2021 at 6:47 pm
If you look at the original post, he checks for month less than 4.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2021 at 6:53 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 17, 2021 at 6:54 pm
Yes that is correct.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2021 at 8:49 pm
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
August 17, 2021 at 9:11 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 17, 2021 at 11:28 pm
Ok... assuming the following requirements...
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
August 18, 2021 at 4:59 am
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 18, 2021 at 6:03 am
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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply