I have a request to calculate a rolling 12 month period when given a date.   I know I can use the AddYears function to easily add a year to any date, but part of the requirement is that the period end must always be the last day of the prior month.   

For example if I'm giving a date of 1/14/2018 my result should NOT be 1/14/2019, it should be 12/31/2018.    Has anyone had to deal with this before, and is there a simple solution that I'm overlooking?

Thanks, 

Josh
#Handlers

------------------------------
Josh Herron
------------------------------

If I'm understanding you correctly, even if the initial date returned is the last day of the month you still want the previous month's last day.

• Break your date time value into three (integers), day, month, year.
• Add 11 to your month value
  • If months > 12, months - 12 and year + 1
  • else just leave it alone because the initial month was January.
• Use a conditional to validate that your date is a valid date:
  • MakeDateUTC(year, month, day) = MakeDateUTC(2018, 13, 52)
  • If your date equals the bad date created on the right side of the equality, you have an invalid date
    • subtract 1 from your day value and try again until it is valid
  • If your date is not equal to the bad date created
    • add 1 to your day until it is invalid, then subtract one.
• Then finally use MakeDate (tz or utc, depending on if you actually need the time, too) on your final values for day, month, year.

This should effectively account for leap year and find the valid last day of the month without needing to know the number of days in the month in advance.  A little convoluted, but it has worked well for me.




------------------------------
Aaron Lael
State of Utah
------------------------------

Original Message:
Sent: 02-20-2019 12:37
From: Josh Herron
Subject: Date manipulation in Handler

I have a request to calculate a rolling 12 month period when given a date.   I know I can use the AddYears function to easily add a year to any date, but part of the requirement is that the period end must always be the last day of the prior month.

For example if I'm giving a date of 1/14/2018 my result should NOT be 1/14/2019, it should be 12/31/2018.    Has anyone had to deal with this before, and is there a simple solution that I'm overlooking?

Thanks,

Josh
#Handlers

------------------------------
Josh Herron
------------------------------

AddDays(dtEndDate, - 1 * (GetDayUTC(dtEndDate))) evaluates to the last day of the month before dtEndDate.  I imagine that you would want to check that dtEndDate is not already the last day.

------------------------------
Tim Cannon
Elevate Credit Services, LLC
------------------------------

Original Message:
Sent: 02-20-2019 12:59
From: Aaron Lael
Subject: Date manipulation in Handler

If I'm understanding you correctly, even if the initial date returned is the last day of the month you still want the previous month's last day.

• Break your date time value into three (integers), day, month, year.
• Add 11 to your month value
  • If months > 12, months - 12 and year + 1
  • else just leave it alone because the initial month was January.
• Use a conditional to validate that your date is a valid date:
  • MakeDateUTC(year, month, day) = MakeDateUTC(2018, 13, 52)
  • If your date equals the bad date created on the right side of the equality, you have an invalid date
    • subtract 1 from your day value and try again until it is valid
  • If your date is not equal to the bad date created
    • add 1 to your day until it is invalid, then subtract one.
• Then finally use MakeDate (tz or utc, depending on if you actually need the time, too) on your final values for day, month, year.

This should effectively account for leap year and find the valid last day of the month without needing to know the number of days in the month in advance.  A little convoluted, but it has worked well for me.




------------------------------
Aaron Lael
State of Utah

Original Message:
Sent: 02-20-2019 12:37
From: Josh Herron
Subject: Date manipulation in Handler

I have a request to calculate a rolling 12 month period when given a date.   I know I can use the AddYears function to easily add a year to any date, but part of the requirement is that the period end must always be the last day of the prior month.

For example if I'm giving a date of 1/14/2018 my result should NOT be 1/14/2019, it should be 12/31/2018.    Has anyone had to deal with this before, and is there a simple solution that I'm overlooking?

Thanks,

Josh
#Handlers

------------------------------
Josh Herron
------------------------------

I'm going to give this a try.   Thanks!

------------------------------
Josh Herron
------------------------------

Original Message:
Sent: 02-20-2019 13:24
From: Tim Cannon
Subject: Date manipulation in Handler

AddDays(dtEndDate, - 1 * (GetDayUTC(dtEndDate))) evaluates to the last day of the month before dtEndDate.  I imagine that you would want to check that dtEndDate is not already the last day.

------------------------------
Tim Cannon
Elevate Credit Services, LLC

Original Message:
Sent: 02-20-2019 12:59
From: Aaron Lael
Subject: Date manipulation in Handler

If I'm understanding you correctly, even if the initial date returned is the last day of the month you still want the previous month's last day.

• Break your date time value into three (integers), day, month, year.
• Add 11 to your month value
  • If months > 12, months - 12 and year + 1
  • else just leave it alone because the initial month was January.
• Use a conditional to validate that your date is a valid date:
  • MakeDateUTC(year, month, day) = MakeDateUTC(2018, 13, 52)
  • If your date equals the bad date created on the right side of the equality, you have an invalid date
    • subtract 1 from your day value and try again until it is valid
  • If your date is not equal to the bad date created
    • add 1 to your day until it is invalid, then subtract one.
• Then finally use MakeDate (tz or utc, depending on if you actually need the time, too) on your final values for day, month, year.

This should effectively account for leap year and find the valid last day of the month without needing to know the number of days in the month in advance.  A little convoluted, but it has worked well for me.




------------------------------
Aaron Lael
State of Utah

Original Message:
Sent: 02-20-2019 12:37
From: Josh Herron
Subject: Date manipulation in Handler

I have a request to calculate a rolling 12 month period when given a date.   I know I can use the AddYears function to easily add a year to any date, but part of the requirement is that the period end must always be the last day of the prior month.

For example if I'm giving a date of 1/14/2018 my result should NOT be 1/14/2019, it should be 12/31/2018.    Has anyone had to deal with this before, and is there a simple solution that I'm overlooking?

Thanks,

Josh
#Handlers

------------------------------
Josh Herron
------------------------------

Thanks!   This is the route I was trying to work through but I thought that there had to be a better way.   Glad I was on the right path.

------------------------------
Josh Herron
------------------------------

Original Message:
Sent: 02-20-2019 12:59
From: Aaron Lael
Subject: Date manipulation in Handler

If I'm understanding you correctly, even if the initial date returned is the last day of the month you still want the previous month's last day.

• Break your date time value into three (integers), day, month, year.
• Add 11 to your month value
  • If months > 12, months - 12 and year + 1
  • else just leave it alone because the initial month was January.
• Use a conditional to validate that your date is a valid date:
  • MakeDateUTC(year, month, day) = MakeDateUTC(2018, 13, 52)
  • If your date equals the bad date created on the right side of the equality, you have an invalid date
    • subtract 1 from your day value and try again until it is valid
  • If your date is not equal to the bad date created
    • add 1 to your day until it is invalid, then subtract one.
• Then finally use MakeDate (tz or utc, depending on if you actually need the time, too) on your final values for day, month, year.

This should effectively account for leap year and find the valid last day of the month without needing to know the number of days in the month in advance.  A little convoluted, but it has worked well for me.




------------------------------
Aaron Lael
State of Utah

Original Message:
Sent: 02-20-2019 12:37
From: Josh Herron
Subject: Date manipulation in Handler

I have a request to calculate a rolling 12 month period when given a date.   I know I can use the AddYears function to easily add a year to any date, but part of the requirement is that the period end must always be the last day of the prior month.

For example if I'm giving a date of 1/14/2018 my result should NOT be 1/14/2019, it should be 12/31/2018.    Has anyone had to deal with this before, and is there a simple solution that I'm overlooking?

Thanks,

Josh
#Handlers

------------------------------
Josh Herron
------------------------------