Date Format Change

[#1]

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

[swuehl]

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 ...;

[Gysbert_Wassenaar]

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

[#4]

hi ,Sakathivel S.

you can use this Function

try it

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

[#5 · Accepted solution]

Hi

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

[#6]

Hi G Wassenaar

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