Convert date problem

[#1]

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

[#2]

Did you set the generic date format?

SET DateFormat='DD.MM.YYY'

--Arun

[blaise]

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.

[#4]

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.

[prieper]

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

[blaise]

interval(Date1 - Date2,'dd') for Number of Days (with optional preciding Zero).

[#7]

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.