SQL datetime formatting function–convert to string

I found this looking for something else on the Internet, but I thought it would be a great item for your toolbox. The function was originally written for 2005, but it has worked on everything I’ve tried.
Enjoy!
Many thanks to Anubhav Goyal , who provided this information to the SQL community on June 11, 2009  - You rock Anubhav! http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
/*-------------------------------------------------------------------------------------------*/
--SQL Server date formatting function --convert datetime to string
/*--------------------------------------------------------------------------------------------*/
--SQL datetime functions
--SQL Server date formats
--T-SQL convert dates
--Formatting dates sql server
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY',
                         DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YY',
                         RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Month',
                         DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, 'MON',
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Mon',
                                     LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MM',
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'M',
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DD',
                         RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'D',
                                     DATENAME(DD, @Datetime))  
RETURN @StringDate
END
GO
--Microsoft SQL Server date format function test
--MSSQL formatting dates
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY')           --01/03/2012
SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY')           --03/01/2012
SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY')            --1/03/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY')             --1/3/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YY')               --1/3/12
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY')             --01/03/12
SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY')         --JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY')         --Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY')       --January 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD')           --2012/01/03
SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD')             --20120103
SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD')           --2012-01-03
--CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD')      --12.01.03
GO

Linking a Sales Tax Detail to a Vendor

Follow this method and you can create PM Transactions to the taxing authorities to remit sales tax, no re-keying required.

Recently I responded to a post on the GP Community Forum from a user wanting to tie a vendor to the sales tax details so that he could automatically create a payables document similar to what is done when you pay something with a credit card.

I thought that was a really good idea and set out to find a way to do it. You need Integration Manager to accomplish it, but it’s easy to set up.

Prerequisites:

Tax Details

You must assign a general ledger account to each Tax Detail you use to calculate tax. The GL account must be unique per tax authority payee.  For example, if I pay the city of Dallas for one tax detail, and pay the county tax assessor for another, you must have two different accounts. This is needed because you are going to assign a vendor to the GL account assigned to the Tax Detail. Each account will represent a single tax collector.

You do not need a unique account for each Tax Detail, just a unique account for each vendor.

Account Maintenance

For each liability account to a Tax Detail, record the Vendor ID in one of the User Defined fields on the Account Maintenance window. I used UserDefined1 in my example.

The Join

You are going to create a SQL statement that matches the account on the Tax Details card with the Account on the Account Maintenance screen. You are also going to match UserDefined1 with the Vendor Master. This will marry a vendor to each tax detail. Now, include whatever tables are needed to retrieve the sales tax amount in whatever period you are working with and use the Integration Manager (or eConnect, etc) to create a payables transaction for the resulting amount.

I used the SQL statement below to retrieve the sales tax detail amount on historical SOP Invoices. I hard coded the date range to select documents with an invoice date between the first day of the previous month and the last day of the previous month, you wouldn’t want to do that. I just put it in here to show how it is done. Voided documents were excluded:

/* This query returns the sales tax amount on historical SOP Invoices with an invoice
   date between the first day of the previous month and the last date of the
   previous month. Voided documents are excluded.
  
   It uses the following tables:
  
   SOP30200    Sales Transaction History
   SOP10105    Sales Taxes Work and History
   GL00100    Account Master
   GL00105    Account Index Master
   PM00200    Vendor Master
   TX00201    Sales/Purchases Tax Master  
*/

SELECT    
  CASE SOP30200.SOPTYPE
     WHEN 1 THEN 'Quote'
     WHEN 2 THEN 'Order'
     WHEN 3 THEN 'Fulfillment Order'
     WHEN 4 THEN 'Invoice'
     WHEN 5 THEN 'Return'
  END AS Doc_Type
, SOP10105.SOPNUMBE AS SOP_Number
, SOP30200.DOCDATE AS Invoice_Date
, SOP30200.CUSTNMBR AS Customer_ID
, SOP30200.CUSTNAME AS Customer_Name
, GL00100.USERDEF1 AS User_Defined1
, PM00200.VENDNAME AS Vendor_Name
, GL00105.ACTNUMST AS GL_Account_Number
, SOP10105.TAXDTLID AS Tax_Detail_ID
, SOP10105.STAXAMNT AS Sales_Tax_Amt
, SOP10105.FRTTXAMT AS Tax_on_Freight
, SOP10105.MSCTXAMT AS Tax_on_Misc
, DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) as FirstDayPrevMo
, DATEADD (d,-DATEPART(d,GETDATE()),GETDATE()) as LastDayPrevMo

FROM

SOP10105 INNER JOIN
        SOP30200 ON SOP10105.SOPTYPE = SOP30200.SOPTYPE
    AND SOP10105.SOPNUMBE = SOP30200.SOPNUMBE
      INNER JOIN GL00100 ON SOP10105.ACTINDX = GL00100.ACTINDX INNER JOIN
                     TX00201 ON SOP10105.TAXDTLID = TX00201.TAXDTLID INNER JOIN
                     PM00200 ON GL00100.USERDEF1 = PM00200.VENDORID INNER JOIN
                     GL00105 ON GL00100.ACTINDX = GL00105.ACTINDX
WHERE    

(SOP30200.SOPTYPE in (3,4)) and  VOIDSTTS = 0 
GO
 
Until next post!

Leslie

First and Last day of Previous Month

In the accounting world I so often need to limit my selection criteria to the first and last day of the previous month. This is certainly not a secret formula, but I now have a place where I can look it up. Sadly, I have not memorized this formula

First Day of Previous Month:

DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE()))

Last Day of Previous Month:

DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Example:

SELECT * FROM SOP30200
WHERE  

VOIDSTTS = 0 and DOCDATE between
DATEADD (m,-1, DATEADD(d,1-DATEPART(d,GETDATE()),GETDATE())) and
DATEADD(d,-DATEPART(d,GETDATE()),GETDATE())

Until next post!

GP2013 Time to Upgrade!!! Service Pack 2 is out!

Yay!!
Service pack 2 for Dynamics GP 2013 has been released.

For everyone waiting for this momentous event, it is here.  Many folks hold out until Service Pack two on the theory that the fixes that didn’t make the ‘release to market’ version are fixed in Service Pack one, but it isn’t until Service Pack two where the real fixes are included that were discovered by actual users.

I am actually a member of the ‘Service Pack two’ crowd .

For partners, you can find the DVD, full install code here: https://mbs.microsoft.com/partnersource/downloads/releases/MDGP2013_Release_Download

For customers, look here: https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP2013_Release_Download

For Partners, If all you’re looking for is the Service Pack 2 patch, look here: https://mbs.microsoft.com/partnersource/downloads/servicepack/MDGP2013_PatchReleases

For Customers, If all you’re looking for is the Service Pack 2 patch, look here: https://mbs.microsoft.com/customersource/downloads/servicepacks/MDGP2013_PatchReleases

The official ‘Fix List’ is here: https://mbs2.microsoft.com/fileexchange/?fileID=07a5d226-5811-48f2-b783-667dd58669f2

Enjoy!

Until next post!

Leslie

Bootstrapper Error

Dynamics GP Forum member named ‘BenefitElect’ shared this solution with us. This is a problem that’s otherwise impossible to track down. Thanks to BenefitElect and Microsoft Tech Support, it will be no problem for us.

A big THANK YOU to BenefitElect for sharing!

The Problem

Here’s the scenario, you’re installing Dynamics GP 2013 on a x64 workstation and you are greeted with the following:

"An error occurred while installing system components for Microsoft Dynamics GP 2013 Bootstrapper.  Setup cannot continue until all system components have been successfully installed."

When you click the details button, the following is displayed:

"Setup has detected that the file C:\Users\User\AppData\Local\Temp\VSDC0C4.tmp\Watson\dw20shared.msi' has either changed since it was initially published or may be corrupt."

The Solution

Here’s the solution from Microsoft Tech Support:

1. First, you will need to navigate to the folder where the setup.exe is for GP 2013. I would like you to copy the Watson folder out of the installation media and paste it into the C:\ root directory on the workstation. The Watson folder should contain three different files: dw20shared.msi, dw20sharedamd64.msi, and dw20sharedia64.msi.

2. Next, click Start > type in cmd and then right-click on the Command Prompt icon in the search results and then left-clicked on "Run as Administrator".

3. In the Command Prompt window, perform a change directory to the Watson folder:

cd\

cd C:\Watson

4. Next, you will need to execute the following command to install Dr. Watson for a 64-bit workstation.

msiexec /i dw20sharedamd64.msi APPGUID={91710409-8000-11D3-8CFE-0150048383C9} /qb+

If this runs successfully, you should be able to go back to the setup.exe to finish the bootstrap installation