topic Re: Date Format Change in QlikView

Date Format Change

Sat, 05 Oct 2013 10:04:08 GMT

Hi,

I am Connecting Sql Server from QV. Given below is my data in sql server. I have to give the date as 01.10.2013, 02.10.2013,03.10.2013 in dimension. But if i change the date format in edit script. It Shows an error as

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'Date' is not a recognized built-in function name. Please Provide me a solution as ASAP and where i have to provide that

Date State Value

10.01.2013 12.00 Madhya Pradesh 10,000.00

10.02.2013 16.00 Andra Pradesh 15,000.00

10.03.2013 20.00 Uttar Pradesh 25,000.00

Re: Date Format Change

swuehl — Sat, 05 Oct 2013 10:12:06 GMT

You can use the QV script functions like Date() or Date#() only within the LOAD part, not within the SQL statement.

I am assuming, your current statement looks like:

SQL SELECT DateField FROM ...;

Then try

LOAD

Date( floor(Date#(DateField,'MM.DD.YYYY hh.mm')),'DD.MM.YYYY') as DateField;

SELECT DateField FROM ...;

Re: Date Format Change

Gysbert_Wassenaar — Sat, 05 Oct 2013 10:13:05 GMT

That sounds like you are trying to use a qlikview function inside an sql statement. Qlikview does not parse sql statements at all, but sends them to the database for execution. That means that the sql statement can only contain functions that the sql database understands. If you want to format the date in sql you'll have to use the cast or convert function. See SQL Server Helper - Tips and Tricks - Date Formats

Re: Date Format Change

Sat, 05 Oct 2013 10:39:40 GMT

hi ,Sakathivel S.

you can use this Function

try it

Date(Date#(DateField, 'YYYYMMDD'), 'M/D/YY').

Re: Date Format Change

Sat, 05 Oct 2013 10:49:59 GMT

Thank you for your suggestion. The function is Date(Date#(mid(pst_date,1,10),'MM/DD/YYYY') ,'DD/MM/YYYY')

Re: Date Format Change

Mon, 07 Oct 2013 04:30:55 GMT

Hi G Wassenaar

Thanks for your suggestion. It was very helpful to me.