Importing data from MySQL server, date format

[#1]

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.

[Anonymous · Accepted solution]

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;

[#3]

try something like this:

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

[Colin-Albert]

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))

[#5]

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