Converted Oracle Months_Between to SQL Server Function?

G

Gordon

Guest
Gordon Asks: Converted Oracle Months_Between to SQL Server Function?
I performed a search here looking for a nice clean solution to the Oracle Months_Between() conversion to SQL Server. I didn't find anything that made sense other than to try to repeat the inaccuracy of the Oracle version. I also searched the web for a little more and found an article on the same thing. Using the code supplied in the article just showed that the guy writing the article didn't know how to test his own code. Let's just say it was scary.

I know this is not so much a question but rather a solution that others might enjoy using. Anyone who has had to use Oracle's Months_Between() function knows that the calculation uses a hard-coded value of 31 and does not include the first day. In some situations, it is nice to have some accuracy. Below is my version of Months_Between() but with 100% accuracy. I still question why Oracle did it that way, or if the Oracle programmer didn't have time to go back and fix the hard coding.

I hope someone out there finds this somewhat useful.

Code:
/*************************************************************************************************
    Name    : fsMonthsBetweenDates
    Purpose : Mimics the Months_Between function in Oracle; except this one is 100% accurate
    Author  : Gordon de Rouyan
    Date    : 2022-09-24
*************************************************************************************************/
CREATE OR ALTER FUNCTION dbo.fsMonthsBetweenDates
(
    @Date1 datetime, 
    @Date2 datetime,
    @InclusiveFirstDay bit = 1
) 
RETURNS float
AS
BEGIN
    -- Using all floats for numeric values
    DECLARE @Months float = 0;

    IF @Date1 IS NOT NULL AND @Date2 IS NOT NULL
    BEGIN
        -- Include the first day or not; e.g. 2021-02-01 and 2021-02-01
        -- Do you want to consider this combination as one day 2021-02-01@00:00:00 to 2021-02-01@23:59:59?
        DECLARE @FirstDayInclusive float = 0;

        IF @InclusiveFirstDay = 1
            SET @FirstDayInclusive = 1;

        -- convert early
        DECLARE @MbdDate1 date = CAST(@Date1 AS date),
                @MbdDate2 date = CAST(@Date2 AS date);

        DECLARE @MdbDate1Day float = CAST(DAY(@MbdDate1) AS float),
                @MdbDate2Day float = CAST(DAY(@MbdDate2) AS float);

        -- Number of days in each month specific to each date; exact with leap year
        DECLARE @Date1MonthDays float = CAST(DAY(EOMONTH(@MbdDate1)) AS float),
                @Date2MonthDays float = CAST(DAY(EOMONTH(@MbdDate2)) AS float);

        -- The fraction of the start month
        DECLARE @StartMonthFraction float = CASE
                                            WHEN @MbdDate1 < @MbdDate2 THEN (@Date1MonthDays - @MdbDate1Day + @FirstDayInclusive) / @Date1MonthDays
                                            ELSE (@Date2MonthDays - @MdbDate2Day + @FirstDayInclusive) / @Date2MonthDays
                                        END;

        -- The fraction of the end month
        DECLARE @EndMonthFraction float =   CASE
                                            WHEN @MbdDate1 < @MbdDate2 THEN @MdbDate2Day / @Date2MonthDays
                                            ELSE @MdbDate1Day / @Date1MonthDays
                                        END;

        -- Force positive value returned
        SET @Months =   CASE
                            WHEN @MbdDate1 <= @MbdDate2 THEN DATEDIFF(MONTH, @MbdDate1, @MbdDate2) - 1 + (@StartMonthFraction + @EndMonthFraction) 
                            ELSE DATEDIFF(MONTH, @MbdDate2, @MbdDate1) - 1 + (@StartMonthFraction + @EndMonthFraction)
                        END;
    END;
 
    RETURN @Months; 
END;

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.
 

Unreplied Threads

How to prove that a po [duplicate]

Swarupananda Dhua Asks: How to prove that a po [duplicate]
How can I prove that if a polynomial of

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.

How to solve Poisson's equation for the potential?

  • AnonAstroZ
  • Physics
  • Replies: 0
AnonAstroZ Asks: How to solve Poisson's equation for the potential?
i'm working with a spherical symmetric system and have found it to have mass density $\rho$ = $\frac{1}{4\pi r^2} \frac{dM(r)}{dr}$.

Poisson's equation says $\nabla^2 \Omega(r) $ = $4\pi G \rho(r)$.

I'm confused on how to solve this equation for the potential $\Omega$ given the $\rho$ that i found. Is there anyone that can advise or provide a starting point? I have been out of school and mathematics for a few years. Thank you!

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.

why do magnetic fields exist as line?

  • SONIC BooM
  • Physics
  • Replies: 0
SONIC BooM Asks: why do magnetic fields exist as line?
why there are no magnetic field lines between two magnetic field lines. why don't magnetic fields exist as a solid Ovid of influence?

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.

What is an intuitive way of understanding the direction of static friction during rolling normally or up or down?

  • Tian Pu
  • Physics
  • Replies: 0
Tian Pu Asks: What is an intuitive way of understanding the direction of static friction during rolling normally or up or down?
some of the answers to the question involved a more detailed analysis, but is there a way to understand the direction without doing so?

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.

How is work done by Tension zero in this case?

  • Nishchal123
  • Physics
  • Replies: 0
Nishchal123 Asks: How is work done by Tension zero in this case?
enter image description here How is the Work done by tension in this case .Theres no horizontal force acting so Com wont displace in horizontal it will be in vertical. But Workdone by tension in m should be T2l/3 costheta and 2m should have been T✓l^2 + 2l^2/3 costheta .How is it zero?

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.

How can I use the Table and Append commands to make Dimen

  • Julissa Velasquez
  • Mathematics
  • Replies: 0
Julissa Velasquez Asks: How can I use the Table and Append commands to make Dimen
I have two sets of data with different dimensions. I would like to use the Table function to create data to fill the dimensions and append it to the set, so both sets have the same dimensions.

I would like to start from the last value and create various x values (up to 2500) while the y can stay the same. So that data2 {917, 2} matches the dimensions of data 1 {1337,2}. Any insight would be greatly appreciated.

Data is here: https://docs.google.com/spreadsheets/d/1Z-tVwtRmt6UD3v67eMO0e5lws8wwZ6G3hLaZJkETSK0/edit?usp=sharing

Code:
 In[1]:= data1 = 
  Import["/Users/julissavelasquez/Box/1_Harrison Lab/03_Formic \
Acid/Wodtke_2021_Fig4C/hyperthermal_dist.xlsx", "SkipLines" -> 2][[1]];

In[2]:= data2 = 
  Import["/Users/julissavelasquez/Box/1_Harrison Lab/03_Formic \
Acid/Wodtke_2021_Fig4C/thermal_dist.xlsx", "SkipLines" -> 2][[1]];

In[3]:= {Dimensions[data1], Dimensions[data2]}

Out[3]= {{1337, 2}, {917, 2}}

In[4]:= data2[[1 ;; 2]]

Out[4]= {{2.9895, 0.00054879}, {5.979, 0.00054814}}

In[5]:= data2[[-2 ;; -1]]

Out[5]= {{2487.29, 2.7924*10^-6}, {2490.28, 2.1354*10^-6}}

In[6]:= Table[data2, {i, 2490.28, 2500}]

Out[6]= If[25048064219406400976 === $SessionID, 
Out[6], Message[
MessageName[Syntax, "noinfoker"]]; Missing["NotAvailable"]; Null]

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.

How can I Prove this? Is the explanation correct?

alwayscurious Asks: How can I Prove this? Is the explanation correct?
The Problem is:- if there is a polynomial-time, additive 80-approximation algorithm for the Minimum Vertex Cover problem, then P = NP.

What i am thinking is, since we already know that the Min vertex cover + 80 vertices which spans vertex cover can be solved in polynomial time and then we need to find V-80 vertices from V ( V is total number of vertices in vertex cover) which is also vertex cover. this can also be done in polynomial time. Therefore the Min. Vertex Cover Problem can be solved in polynomial time. Therefore, P=NP.

I feel like this is wrong explanation. Any help is appreciated.

SolveForum.com may not be responsible for the answers or solutions given to any question asked by the users. All Answers or responses are user generated answers and we do not have proof of its validity or correctness. Please vote for the answer that helped you in order to help others find out which is the most helpful answer. Questions labeled as solved may be solved or may not be solved depending on the type of question and the date posted for some posts may be scheduled to be deleted periodically. Do not hesitate to share your thoughts here to help others.