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

W-2 Other Codes for Box 12

 

With the W-2 season upon us, I thought now would be a good time to point out some items that should live in Box 12; but often don’t make it there. I’m not attempting to give any tax or accounting advice, just to share some information with you about Box 12.

Box 12 looks simple enough, but oh what a secret she keeps:

Recently, I posted an article about common errors regarding retirement account information. In this post, I’ll review the items that should be reported in box 12. In the 30 years I’ve been working with Forms W-2 in various capacities, I have never seen an employer follow all of the items the IRS says should be included in box 12. 

The table below comes from the W-2 instructions on the form itself:

Box 12 Code	Description
A	Uncollected Social Security or RRTA tax on tips
B	Uncollected Medicare tax on tips
C	Taxable cost of group-term life insurance over $50,000
D	Elective deferrals to a section 401(k) cash or deferred arrangement. Also includes deferrals under a SIMPLE retirement account that is part of a section 401(k) arrangement
E	Elective deferrals under section 403(b) salary reduction agreement with your employer
F	Elective deferrals under a section 408(k)(6) salary reduction SEP
G	Elective deferrals and employer contributions (including non-elective deferrals) to a section 457(b) deferred compensation plan
H	Elective deferrals to a section 501c(18)(D) tax-exempt organization plan
J	Nontaxable sick pay (information only, not included in boxes 1, 3, or 5)
K	Twenty percent (20%) excise tax on excess golden parachute payments
L	Substantiated employee business expense reimbursements (nontaxable)
M	Uncollected social security or RRTA tax on taxable cost of group-term life insurance over $50,000 (former employees only)
N	Uncollected Medicare tax on taxable cost of group-term life insurance over $50,000 (former employees only)
P	Excludable moving expense reimbursements paid directly to employee (not included in boxes 1, 3, or 5)
Q	Nontaxable combat pay
R	Employer contributions to your Archer MSA.
S	Employee salary reduction contributions under a section 408(p) SIMPLE plan (not included in box 1)
T	Adoption benefits (not included in box 1)
V	Income from exercise of non-statutory stock option(s) (included in boxes 1, 3 (up to social security wage base), and 5)
W	Employer contributions (including amounts the employee elected to contribute using a section 125 (cafeteria) plan) to your health savings account
Y	Deferrals under a section 409A nonqualified deferred compensation plan
Z	Income under section 409A on a nonqualified deferred compensation plan. This amount is also included in box 1. It is subject to an additional 20% tax plus interest.
AA	Designated Roth contributions under section 401(k) plan
BB	Designated Roth contributions under section 403(b) plan
DD	Cost of employer-sponsored health coverage (this is not taxable when Code DD)
EE	Designated Roth contributions under a governmental section 457(b) plan. This amount does not apply to contributions under a tax-exempt organization section 457(b) plan.

I have highlighted three of the items that I often see overlooked by employers.

Code C – taxable cost of group-term life insurance over $50,000

If an employer provides life insurance for their employees, and the death benefit is more than $50,000, an amount should be included in wages based an an IRS formula.

In most cases, the cost of up to $50,000 of group-term life insurance coverage provided by an employer is not included in income. However, the cost of employer-provided coverage over $50,000 should be included in the taxable income of the employee.

This taxable amount is subject to withholding for FICA and Medicare, but not Federal Income tax.

For example, let’s say a 50 year-old employee was provided a $200,000 life insurance policy. The first $50,000 of coverage is not taxable, however, the remaining $150,000 is taxed based on the IRS’s calculation. The IRS numbers are in the following table:

Age	Taxable Cost per $1,000, per month
Under 25	0.05
25 - 29	0.06
30 - 34	0.08
35 - 39	0.09
40 - 44	0.10
45 - 49	0.15
50 - 54	0.23
55 - 59	0.43
60 - 64	0.66
65 - 69	1.27
70 and older	2.06

Using the above table, this employee should have an additional $414.00 included in box 1 and box 12 with code C. He had an extra $150,000 of insurance for 12 months. The calculation would be 150 x 12 x .23 = 414.00.

Code L – substantiated employee business expense reimbursements

This one is real shocker to most people. Any time your employee submits an expense report that is supported with receipts, or conforms to the governmental per diem or mileage rates, the amount that you reimburse him or her should be included in box 12 of Form W-2 with code ‘L’. The amount is not taxable, and therefore not included in Box 1; but it should be reported in box 12. As I understand it, the theory behind this is that it gives the IRS a heads up should the employee try to deduct the expenses on Form 2106 (Employee Business Expense) on his personal 1040.

Code P – excludable moving expense reimbursements

The amount under code P is the amount you reimbursed your employee for ‘qualified’ moving expenses that were not includable in their taxable income. The amount in this box is not included in boxes 1, 3 or 5. If the employees ‘qualified’ moving expenses exceeded the code P amount, they may be able to deduct the extra expenses on Form 3903 filed with their Form 1040. The code P amount does not need to be included anywhere on the Form 1040 if Form 3903 is not filed with the return.

As in all IRS rules, there are pages of additional information regarding what should be reported where. The above is certainly not an authoritative document. If you think any of this might apply to you, contact your accounting/tax advisor for additional information.

Good luck with tax season.

Live the dream!

Leslie

Dynamics GP Excel Dashboard available now!

 

This comes to us from one of Dynamics GP’s finest, the infamous Mark Polino! Jump to  www.DynamicAccounting.net/Analytics to get more details.

This is the truly painless way to get started with Excel dashboards, have someone else do it for you . Imagine, an Excel dashboard with all of the bells and whistles ready to go. We’ve all seen and heard how cool dashboards are, and wish we had the time to figure out how to create them. Well, the time has been reduced to just a few minutes. Seriously, the dashboard is easy to install; and, honestly, I do mean easy.

Simplicity is a word that appeals to me, and Mark’s Excel dashboard falls into that category. One of the things I like to point out during my development/tool classes is that you should ALWAYS look around to see if anyone else has already created the thing you are wanting to create. So many times, the answer is yes.  This is one of those yes times.

If you want to get started, but can’t carve out the hours it takes to develop this anew, this is for you.

Though the dashboard is perfectly fine just like it is, you have the option to change it however you want. We all know that changing something is far easier than creating it from scratch.

Can you say plug and play?  I am among the laziest people out there. I know the macro language so well because it saves me time and clicks.  If I can save clicks, I’m all for it! This is my kind of dashboard; just connect it to your data source and you’re ready to go. And fear not, if data sources are not your area of expertise, Mark makes that easy too.

So what’s available? Mark has released a dashboard for the Sales series. Here’s what it includes:

● Top Customers with corresponding costs               
● Top Items with corresponding costs               
● Top Salespeople, Territories and Sites               
● Sales over time with forward looking projection               
● Sales mix over time by item class               
● A full range of data filters to control information

Click the link and check it out.

How many of these do you think Mark can develop in 50 minutes?   

Until next post!

Leslie