Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Teresa Waters]

I have a MO Receipts summary table where it appears the dates are coming in as text fields.  Is there anyway to easily change this so we can apply date filters?

——————————
Teresa Waters
Controller
La Crosse Milling Company
Cochrane WI
——————————

[John Arnold]

Hi Teresa,

Can you share the query that’s being run?

Thanks

——————————
John Arnold
Senior Software Engineer
US Digital
Vancouver WA
——————————
——————————————-

[Teresa Waters]

Hi John,

Here is what is in the spreadsheet:

 select [MOP Receipt Number],[Manufacture Order],[Item Number],[Item Description],[Date Received],[Serial/Lot QTY],[Serial/Lot Number],[Expiration Date] from [LCM].dbo.erbGPMMOReceiptsSummaryv2

——————————
Teresa Waters
Controller
La Crosse Milling Company
Cochrane WI
——————————
——————————————-

[John Arnold]

Ah, the fun begins… The question now is, what’s erbGPMMOReceiptsSummaryv2?  Is it a table or a view (I’m guessing it’s a view)?

Do you have access to the SQL behind view?

——————————
John Arnold
Senior Software Engineer
US Digital
Vancouver WA
——————————
——————————————-

[John Kirsch]

Hi Teresa,

If you are looking to convert the field in a TSQL command, you can use the cast or convert functions.  Here are some examples using a text string which you can run in SQL.  When you find the format you like, you can replace the text string with the field name.    I prefer the last one as it will return the date in the format of 1/5/21 for that example.  

Select ‘1-5-21’
Select Cast(‘1-5-21’ as date)
Select Cast(‘1-5-21’ as datetime)
Select Format(Cast(‘1-5-21′ as datetime),’M/d/yy’)

From the name of the object – it appears that it may have been built in Excel Report Builder so you may want to make changes in that tool rather than in TSQL.

——————————
John Kirsch
GP Product Lead
Dynamic Consulting
Green Bay WI
——————————
——————————————-

[John Arnold]

Hi John,

To start with, you have a great first name!  😉

In my experience, I highly recommend “not” casting the date to the format you want to appear on a report or in excel.  Reporting tools and Excel let you format the date to how you want it displayed.  If you format it in the query, the data being sent to the report or excel can end up being a string (VARCHAR) format instead of a date.  It may display as a date but you can’t do any of the cool date things (group by date in pivot table for example) unless it’s a date.

And to be totally correct, excel likes SQL DATETIME data types.  It doesn’t always like SQL DATE datatypes.

Thanks

——————————
John Arnold
Senior Software Engineer
US Digital
Vancouver WA
——————————
——————————————-

[John Arnold]

Hi Teresa,

The query you sent was very helpful!  My suspicions were confirmed.

The query had:  RTRIM(CAST(DATEPART(mm, MOP1100.DATERECD) AS VARCHAR(2))) + ‘/’ + RTRIM(CAST(DATEPART(dd, MOP1100.DATERECD) AS VARCHAR(2))) + ‘/’ + RTRIM(CAST(DATEPART(yy, MOP1100.DATERECD) AS VARCHAR(4))) AS [Date Received],

to make the Date Received look pretty.  The only problem, it converts the data type from DateTime to a VARCHAR (string).  Excel wants the date time – it can then display it or filter on it as you’d like.  You can fix this by just returning the DATERECD field.  Note: The same problem exists with the Expiration Date field (EXPNDATE).

If Excel is the only place using this query, there should be no problems updating the query.  If it’s used by a report somewhere, it may be problem there.

This query should work for you:  (The — at the beginning of the two lines comments out that line)

SELECT MOP1100.MOPRCTNM AS [MOP Receipt Number],
MOP1100.MANUFACTUREORDER_I AS [Manufacture Order],
WO010032.ITEMNMBR AS [Item Number],
IV00101.ITEMDESC AS [Item Description],
MOP1100.DATERECD AS [Date Received],
–RTRIM(CAST(DATEPART(mm, MOP1100.DATERECD) AS VARCHAR(2))) + ‘/’ + RTRIM(CAST(DATEPART(dd, MOP1100.DATERECD) AS VARCHAR(2))) + ‘/’ + RTRIM(CAST(DATEPART(yy, MOP1100.DATERECD) AS VARCHAR(4))) AS [Date Received],
MOP1040.SERLTQTY AS [Serial/Lot QTY],
MOP1040.SERLTNUM AS [Serial/Lot Number],
MOP1040.EXPNDATE AS [Expiration Date]–,
–RTRIM(CAST(DATEPART(mm, MOP1040.EXPNDATE) AS VARCHAR(2))) + ‘/’ + RTRIM(CAST(DATEPART(dd, MOP1040.EXPNDATE) AS VARCHAR(2))) + ‘/’ + RTRIM(CAST(DATEPART(yy, MOP1040.EXPNDATE) AS VARCHAR(4))) AS [Expiration Date]
FROM dbo.MOP1100 WITH (NOLOCK)
INNER JOIN dbo.WO010032 WITH (NOLOCK)
ON WO010032.MANUFACTUREORDER_I = MOP1100.MANUFACTUREORDER_I
INNER JOIN dbo.IV00101 WITH (NOLOCK)
ON IV00101.ITEMNMBR = WO010032.ITEMNMBR
INNER JOIN dbo.MOP1040 WITH (NOLOCK)
ON MOP1040.DOCNUMBR = MOP1100.MOPRCTNM

Good Luck!

——————————
John Arnold
Senior Software Engineer
US Digital
Vancouver WA
——————————
——————————————-

[John Arnold]

Hopefully, this is more readable…

SELECT	MOP1100.MOPRCTNM AS [MOP Receipt Number],
		MOP1100.MANUFACTUREORDER_I AS [Manufacture Order],
		WO010032.ITEMNMBR AS [Item Number],
		IV00101.ITEMDESC AS [Item Description],
		MOP1100.DATERECD AS [Date Received],
		--RTRIM(CAST(DATEPART(mm, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(dd, MOP1100.DATERECD) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(yy, MOP1100.DATERECD) AS VARCHAR(4))) AS [Date Received],
		MOP1040.SERLTQTY AS [Serial/Lot QTY],
		MOP1040.SERLTNUM AS [Serial/Lot Number],
		MOP1040.EXPNDATE AS [Expiration Date]--,
		--RTRIM(CAST(DATEPART(mm, MOP1040.EXPNDATE) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(dd, MOP1040.EXPNDATE) AS VARCHAR(2))) + '/' + RTRIM(CAST(DATEPART(yy, MOP1040.EXPNDATE) AS VARCHAR(4))) AS [Expiration Date]
FROM	dbo.MOP1100 WITH (NOLOCK)
JOIN	dbo.WO010032 WITH (NOLOCK)
	ON	WO010032.MANUFACTUREORDER_I = MOP1100.MANUFACTUREORDER_I
JOIN	dbo.IV00101 WITH (NOLOCK)
	ON	IV00101.ITEMNMBR = WO010032.ITEMNMBR
JOIN	dbo.MOP1040 WITH (NOLOCK)
	ON	MOP1040.DOCNUMBR = MOP1100.MOPRCTNM?

——————————
John Arnold
Senior Software Engineer
US Digital
Vancouver WA
——————————
——————————————-

[Jeff Hassenboehler]

If you don’t have luck with the back-end SQL date correction steps you can try this low-tech method:
In your Excel file, just add a new column to hold the date in the correct format and add a simple formula such as “=B2+0”.  The table will convert it to read something like “=[@[TRX DATE]]+0” and it will automatically copy down for all rows of your Excel Refreshable Report.  Then just format data in that column in a date format.  Use this new field in your pivot tables instead of the original “text” data.

jph

——————————
Jeff Hassenboehler
Definition6
Atlanta GA
——————————
——————————————-

[Teresa Waters]

Thanks,
I looked at the SQL fix and chickened out, but the adding a calculated column in the excel report worked great and I was able to add a pivot table and restrictions there.
Thanks everyone for your help!!!!

——————————
Teresa Waters
Controller
La Crosse Milling Company
Cochrane WI
——————————
——————————————-