topic Convert date problem in QlikView

Convert date problem

Thu, 15 Oct 2009 17:10:20 GMT

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

Convert date problem

Thu, 15 Oct 2009 17:15:55 GMT

Did you set the generic date format?

SET DateFormat='DD.MM.YYY'

--Arun

Convert date problem

blaise — Thu, 15 Oct 2009 17:26:22 GMT

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.

Convert date problem

Thu, 15 Oct 2009 19:10:42 GMT

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.

Convert date problem

prieper — Thu, 15 Oct 2009 19:16:35 GMT

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

Convert date problem

blaise — Thu, 15 Oct 2009 19:17:05 GMT

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

Re: Convert date problem

Wed, 25 Jul 2012 13:21:58 GMT

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.