topic Re: Importing data from MySQL server, date format in QlikView

Importing data from MySQL server, date format

Fri, 24 Oct 2014 13:35:25 GMT

Hi,

I've a .csv file with a date column, in the format 01/01/1900. By using the following: DATE(DATE, 'DD-MMM-YY') AS DMY, it turned it into the format 01-Jan-00, which is what I want, it worked just fine. Now I'm importing the exact same data from a MySQL server instead of reading from a .csv, and the above line of code now returns an error. It says the syntax is wrong, something about the version of MySQL. Is there some different syntax that needs to be used when importing data from MySQL? I tried DATE_FORMAT(DATE, %...) but that just gave me errors too.

Re: Importing data from MySQL server, date format

Anonymous — Fri, 24 Oct 2014 13:38:47 GMT

Try explicitly casting or converting the value of the date field as part of the SQL statement.

Load

...

DATE(DATE, 'DD-MMM-YY') AS DMY

;

SQL Select

...

cast(DATE as Date) as DATE,

...

from table;

Re: Importing data from MySQL server, date format

Fri, 24 Oct 2014 13:39:55 GMT

try something like this:

MakeDate(right(DATE,4),mid(DATE,4,2),left(DATE,2))

Re: Importing data from MySQL server, date format

Colin-Albert — Fri, 24 Oct 2014 13:46:31 GMT

The function to convert dates that are held as text to a true date in QlikView is Date#

date(date#(date_string, date_format))

e.g. date(date#('17-25-14', 'MM-DD-YY))

Re: Importing data from MySQL server, date format

Fri, 24 Oct 2014 13:49:49 GMT

Thanks all for the quick replies, will try these out. Quite new to QlikView and SQL.