Date Math – Oracle vs. SQL Server

mathclub_imagesF2SCPZGB

Series of posts dedicated to comparing the similarities and differences between PL SQL and T-SQL on the subject of Date Math calculations.

Unlike the rules of FIGHT CLUB, we will be talking about MATH CLUB.  This post is provided in the sincere hope that you do not have to “tap out” because you are scrambling to find a quick solution to your date math fight in PL SQL.  Of course, if you are at a client site, shirt and shoes are likely required (I hope!).  The fight will have to go on until you find the right solution.  If this is your first math date fight, you must fight…and it probably won’t be your last.

With recent project work, I have been primarily focused on building data processing solutions using the Oracle 10g platform with the PL SQL Developer tool.  While most of my professional career I have been focused on developing SQL Server solutions, there came along the project that was not focused on SQL Server at all.  This post is provided in the sincere hope that if you are in a similar situation you can use the provided examples and resources to shorten your development time.

Calculating Age

The following examples are show to compare how you would calculate a persons age using PL SQL vs. T-SQL.

PL SQL Example

Using PL SQL, there are several functions that must be used in order to provide the date parameters in a format to ultimately perform integer-based math.  Without the application of these functions, the math is performed on the decimal values returned and does not provide the desired integer value (1, 25, 56, etc.) for age.  We will cover these functions in total here.

PL SQL Statement – To Calculate Age

floor(months_between(SYSDATE, birth_dt)/12)

PL SQL Functions Explained

months_between – returns the number of months between the first and second date parameter.

floor – returns the largest integer value that is equal to or less than a value.  In other words, the decimal portion of the number is truncated and provides an integer value.

Using a small data set, we can apply the months_between function to the SYSDATE and BIRTH_DT columns to reveal how the values will be returned.

MonthsBetween_PLSQL

Now with the application of the FLOOR function the values now appear as

       MonthsBetween_floor_PLSQL

Finally, dividing the MONTHS_BETWEEN values by 12 will render the age of our participants.

       Age_PLSQL

T-SQL Example

In the SQL Server world this is drastically simpler primarily because only one native function is required to provide the results we saw above months between and age.  The DATEDIFF function is the only function required to accomplish both result sets.  The only difference is the argument value we pass to the function.

T-SQL Statement – To Calculate Months Between

T-SQL Months Between

Months Between Data

T-SQL Statement – To Calculate Age

T-SQL DateDiff for Age

Age Data Set

T-SQL DateDiff Function Explained

The DATEDIFF function returns a positive integer value based on the boundaries established by the datepart argument.

DATEDIFF ( datepart , startdate , enddate )

datepart – is part of the startdate and enddate that specifies the type of boundary (year, month, day, etc.) to cross.

startdate – an expression that can resolve to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset

enddate – see startdate

 

Be the first to comment

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.