TSQL - Fun with Dates!

Are you constantly needing to come up with different relative dates in reports, SmartLists and the like? I found this great list of date calculations from Pinal Dave and Vivek Jamwal. I’m duplicating it here because 1) I’ll be able to find it, and 2) I wanted to share my find with the Dynamics GP community.

Brenner Klenzman provided the formulas marked ** so that they would render correctly for GP data.

Please note: some of the formulas in the table can be replaced using the new functions in SQL 2012 – links below table.

At the end of this post, I have included several links to various DateTime related articles and additional notes. Also included, are links to articles about the new DateTime functions in SQL 2012!

Enjoy, and thanks again to Pinal Dave, Vivek Jamwal and Brenner Klenzman.

Date	Code	Results on 02-05-2014
Current date and time	SELECT Getdate()	2014-02-05 07:10:49.533
First day of current month	SELECT Dateadd(m, Datediff(m, 0, Getdate()), 0)	2014-02-01 00:00:00.000
**Last day of current month	SELECT Dateadd(m, Datediff(m, 0, Getdate()) + 1, 0) - 1	2014-02-28 00:00:00.000
First day of previous month	SELECT Dateadd(m, -1, Dateadd(m, Datediff(m, 0, Getdate ()), 0))	2014-01-01 00:00:00.000
Last day of  previous month	SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0, Getdate ()), 0))	2014-01-31 00:00:00.000
First day of  next month	SELECT Dateadd(m, 1, Dateadd(m, Datediff(m, 0, Getdate )), 0))	2014-03-01 00:00:00.000
Last day of next month	SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0 Dateadd (m, 2,Getdate())), 0))	2014-03-31 00:00:00.000
Last day of month 2  months ago	SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0, Dateadd(m, -1, Getdate())), 0))	2013-12-31 00:00:00.000
Last day of prev month in the previous year	SELECT Dateadd(d, -1, Dateadd(m, Datediff(m, 0, Dateadd(m, -12, Getdate())), 0))	2013-01-31 00:00:00.000
Monday of current week	SELECT Dateadd(wk, Datediff(wk, 0, Getdate()), 0)	2014-02-03 00:00:00.000
Sunday of current week	SELECT Dateadd(wk, Datediff(wk, 0, Getdate()), 6)	2014-02-09 00:00:00.000
Fourth Monday of current month	SELECT CASE WHEN( 2 - Datepart(dw, Dateadd(m, Datediff(m, 0, Getdate()), 0)))) >= 0 THEN Dateadd(wk, 3, ( Dateadd(m, Datediff(m, 0, Getdate()), 0)) + (2 - Datepart(dw, ( Dateadd(m, Datediff(m, 0 , Getdate()), 0))))) ELSE Dateadd(wk, 4, ( Dateadd(m, Datediff(m, 0, Getdate()), 0 )) + ( 2 - Datepart(dw, ( Dateadd (m, Datediff(m, 0,     Getdate()), 0))))) END	2014-02-24 00:00:00.000
Second Thursday of current month	SELECT CASE WHEN (5 - Datepart(dw, (Dateadd(m, Datediff(m, 0, Getdate()), 0)))) >= 0 THEN Dateadd(wk, 1, ( Dateadd(m, Datediff(m, 0, Getdate()), 0) ) + ( 5 - Datepart(dw, (Dateadd(m, Datediff(m, 0,                   Getdate()), 0))))) ELSE Dateadd(wk, 2, (Dateadd(m, Datediff(m, 0, Getdate()), 0 )) + ( 5 - Datepart(dw, (Dateadd (m, Datediff(m, 0, Getdate()), 0))))) END	2014-02-13 00:00:00.000
First day of current year	SELECT Dateadd(yy, Datediff(yy, 0, Getdate()), 0)	2014-01-01 00:00:00.000
**Last day of current year	SELECT Dateadd(yy, ( Datediff(yy, 0, Getdate())+ 1 ), 0) - 1	2014-12-31 00:00:00.000
First day of previous year	SELECT Dateadd(yy, -1, Dateadd(yy, Datediff(yy, 0, Getdate()), 0))	2013-01-01 00:00:00.000
**Last day of previous year	SELECT Dateadd(yy, ( Datediff(yy, 0, Getdate())), 0) - 1	2013-12-31 00:00:00.000
First day of April in the current year	SELECT Dateadd(m, 3, Dateadd(yy, Datediff(yy, 0, Getdate()), 0))	2014-04-01 00:00:00.000
First day of April in the previous year	SELECT Dateadd(m, 3, Dateadd(yy, Datediff (yy, 0, Dateadd(yy, -1, Getdate())), 0))	2013-04-01 00:00:00.000
**	With thanks to Brenner Klenzman at Willoware for the correct statements.

Notes from Pinal Dave: All of the code above, (except Brenner’s, emphasis added) uses Getdate(), or the current date, as a starting point. To test these, just copy what’s in the ‘Code’ column above into a SQL query and execute it. If you need to use a parameter instead, you can replace Getdate() with your parameter name. Be careful with the calculations for weeks. The examples above are assuming that the first day of the week is Sunday. You can check whether this is the same on your SQL Server by running the following query:

        SELECT @@datefirst 

•  If it returns 7, then your first day of the week is Sunday.
• Don’t forget to test this code before you rely on it.

Notes from Brenner Klenzman: Some standard SQL date functions can cause problems when used with GP data.

For example: On Feb 3^rd of 2014, GETDATE()does not yield 02/03/2014, it actually includes the time down to milliseconds.  When executed, it yields:

2014-02-03 04:27:55.053

If you select documents >= getdate()-7, you are not going to get documents from 01/27/2014 because all documents in GP on that date actually have the date: 2014-01-27 00:00:00.000

Likewise, “Last day of current month” does not yield a GP-compatible “last day” if you use: select DATEADD(ms, -3, DATEADD(m, 0, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))) This formula yields:

2014-02-28 23:59:59.997

What you really want is:

2014-02-28 00:00:00.000

Additionally, if you have a query where 3 milliseconds is being subtracted, you get the same result when you subtract 2 milliseconds. For example: subtracting either 2 or 3 milliseconds yields 2013-12-31 24:59:59.997.  If you subtract 1 millisecond, you get 2014-01-01 00:00:00.000. The final digit in milliseconds is always 0, 3 or 7 (i.e. .880 or .883 or .887), you will never get 884. http://msdn.microsoft.com/en-s/library/ms187819.aspx

Thank you to Pinal Dave and Brenner Klenzman for information included in this post.

Boring notes about DateTime:

The SQL datetime data type is only accurate to 1/333 of a second (3.33ms) or 333 nanoseconds. A nanosecond is 1/100^th of a second. A nanosecond is a unit known as a ‘tick’. A particular date is the number of ticks since 12:00 midnight on 1/1/0001 A.D.. The SQL DateTime’s accuracy is to the nearest increment of .000, .003 or .007 seconds. That’s why, in Brenner’s example, he got the same answer when he subtracted 2 milliseconds that he did when he subtracted 3 milliseconds.

SQL 2008 introduced the datetime2 data type. This data type is accurate to 1/100^th of a second. Nevertheless, the datetime2 data type is not used in Dynamics GP.

Here are some links to other helpful DateTime articles:

SQL datetime formatting function–convert to string

Part 1 Working with SQL Server DateTime Variables

Part 2 Displaying Dates and Times in Different Formats

Part 3 Searching for Particular Date Values and Ranges

Part 4 Date Math and Universal Time

SQL SERVER 2012 – Date and Time Functions

• DATEFROMPARTS (year, month, day)
• DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
• DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
• DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
• SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
• TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
• EOMONTH (start_date)

Until next post!

Leslie

2013 FICA-Medicare withheld only prints $2,900 on W-2

Only $2,900. . .what’s up with that? As we all are frantically preparing our W-2s, an anomaly (a bug, really) has reared its head. It applies to those employees with FICA Medicare wages in excess of $200,000. As you know, the 2.35% Medicare rate kicks in at that amount. The extra withholding is calculated correctly on the pay checks and everything looks right on the Year End Wage report.

The Problem

Although the year end wage report is correct, when the W-2s print, the amount in the Medicare withheld box is only $2,900. Suppose this employee made $350,000 and really had $6,425 in Medicare withholding. Eeek! Chances are, the folks making more than $200,000 on the salary scale, are pretty high up in the organization. These are not the people to whom you want to give an incorrect W-2.  So what happened to the extra $3,525 that was withheld, and how do you get it on the W-2?

How it happens

We had a similar problem when the tax rates were split between employer and employee in 2011/2012 and the following year’s tax tables were loaded before the current year payroll was closed. I discuss that problem on the post How is FICA/S wages and withholding calculated?.

While this problem manifests itself in the same way, it isn’t because the tax tables were not properly loaded. It has to do with how Dynamics GP calculates the amount that goes into the Medicare withheld box.

When you close the payroll year, the system creates the Payroll Year-End Header record in the UPR10100 table. It also adds a record for each employee to the Payroll Year End Wage table UPR10101. The numbers in the Payroll Year End Wage table are taken from the Payroll Employee Summary (UPR00900) and therefore reflects the actual amounts.

When you close the payroll year, the Payroll Year End Header (UPR10100) table is populated. Among the information pulled into this table, are the FICA/SS and FICA/M rates and wage limits. The numbers come from whatever values are in the tax tables at the time of the close. The  Payroll Year-End Wage (UPR10100)  table is also populated at this time. The Year-End Wage table gets its numbers from the Payroll Employee Summary table, that table holds the actual amounts from the employees’ paychecks. The Year End Employee Wage Report is created from the Year-End Wage table and that’s why it shows the full amount of the FICA/M withheld. The year end wage report does not read the FICA/M or FICA/SS amounts from the header.

The W-2 Validation report calculates its amounts on the fly when you print the report. The Validation report gets its rates from the tax table itself. Here’s the rub – there is only one field in the header table in which to store the FICA rate (TICAMTRT). The Medicare rate is pulled from the first row of the FICA/M tax table. Therefore, the rate recorded for 2013 was 1.45%. Another field in the header table (FICAMWLM) stores the Medicare wage limit . Sometimes, the value of $200,000 fills the FICAMWLM field. I do not know the set of circumstances that cause this.

When you print W-2s, the system calculates the maximum amount of Medicare withholding allowed by law based on the wage limit and rate stored in the header table. Dynamics GP will not print a number greater than this calculated maximum on the the W-2, regardless of how much was actually withheld. If the FICAMWLM field has a value of $200,000 in it, Medicare withheld on the W-2 will be limited to $2,900 (.0145 * $200,000).

The Fix

Fortunately, this is an easy problem to fix. All you need to do is set the value of UPR10101.FICAMWLM to zero. This SQL statement will accomplish the task: Update UPR10101 set FICAMWLM = 0

With no wage limit, there is no maximum. Your W-2s will now print with the full amount of Medicare withheld.

Until next post!

Leslie