Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm exporting data from MS Dynamics AX 4.0 into excel and then into QV.
The date format I get from Axapta is e.g. 21.01.2009. When I import it into QV it is not recognised as a date. How do I change this into a workeable date format so I can make calculations with it (count the number of days between 2 dates)?
Thank you
Herman Van der planken
Did you set the generic date format?
SET DateFormat='DD.MM.YYY'
--Arun
you could also use the date(date#(field,'DD.MM.YYY'),'YYYY-MM-DD') to get ISO dates. Inside the date() you specify what the result should be like, and the Date#() explains how the field is structured.
Thank you, it worked (the Date# method)
But how can I now count the number of days between two (converted) dates ?
I used to convert the date in excel and then in QV i just had to deduct the one date from the other.
The answer from Blaise will fit perfect to your problem: Date#() reads a string and considers it as Number, Date() ensures to display it as per the predefined date-formatting. Already with having the figures with date# any subtraction will work.
HTH
Peter
interval(Date1 - Date2,'dd') for Number of Days (with optional preciding Zero).
Hi,
The usual way to get MDAx date is using SQL Server Connetor insted excel who has serveral problems. If you need information about data dictionary contact whit me.
I hope to help you.