topic Re: How to change date format in QlikView

How to change date format

Mon, 13 Jan 2014 14:15:59 GMT

Hi,

I have to reload script as below:

Table:

LOAD ID,

FinancialYr,

SQL SELECT *

FROM company

WHERE Datecreated>'03/12/2012';

I am getting error as Date format mismatch when loading this

pleasecan anyone help me how to change this.

thanks.

Re: How to change date format

Michiel_QV_Fan — Mon, 13 Jan 2014 14:18:20 GMT

Define your date format as needed with the date syntax:

Date('Your field', 'DD/MM/YYYY')

Re: How to change date format

Mon, 13 Jan 2014 14:23:48 GMT

Hi,

Thanks and it is showing below error when I am using this please help

Wrong number of arguments used with function in query expression 'Date(DateLogged,'DD/MM/YYYY')>'03/07/2013'.

Re: How to change date format

Michiel_QV_Fan — Mon, 13 Jan 2014 14:49:55 GMT

Maybe a datetime format?

If DateLogged is datetime you can add floor to the statement to loose the timepart:

Date(floor(DateLogged, 'DD/MM/YYYY') > '03/07/2013'

Re: How to change date format

Anonymous — Mon, 13 Jan 2014 15:18:39 GMT

convert(varchar(30),getdate(),10) 10 denotes formats,change different value for 10 you will get your format

Re: How to change date format

Mon, 13 Jan 2014 15:22:27 GMT

This link may help you convert your format.

http://sqlusa.com/bestpractices/datetimeconversion/

Re: How to change date format

Mon, 13 Jan 2014 16:08:18 GMT

If your DB is SQL Server, SQL SELECT * FROM company WHERE Datecreated>'03/12/2012'; should work

If your DB is Oracle, SQL SELECT * FROM company WHERE Datecreated>TO_DATE('03/12/2012','DD/MM/YYYY');

should work.

Re: How to change date format

Colin-Albert — Mon, 13 Jan 2014 20:16:58 GMT

I would recommend using dates formatted as 'YYYY-MM-DD' when passing dates to SQL as this format does not have any confusion between Months & Days.

A date '03/01/2013' can be read as Jan 3rd 2013 or March 1st 2013 depending if your in Europe or USA.

Also check the DateFormat string on the first tab of your QlikView load script which shows the system date setting, but this may not match how SQL is configured!

Try

SQL SELECT *

FROM company

WHERE Datecreated > '2012-12-03' ;