topic Re: SQL error when converting the date format from yyyymmdd to dd/mm/yyyy in QlikView

SQL error when converting the date format from yyyymmdd to dd/mm/yyyy

Lily — Tue, 31 May 2022 04:24:15 GMT

I'm having difficulty converting date format from yyyymmdd (e.g.: 20210515) to dd/mm/yyyy (e.g.: 15/05/2021) .

I tried several QV community sample scripts to convert the date but still unsuccessful. Here's an example load script that I tried:

i) Date(Date#("ERDAT",'YYYYMMDD'),'MM/DD/YYYY') as "CreatedOnDateNew",
ii) Date(alt(Date#("ERDAT", 'YYYYMMDD')), 'DD/MM/YYYY') as "CreatedOnDateNew",

My load script:

Load *;
SQL Select
"AUFNR" as "OrderNo",
"AUART" as "OrderType",
"WERKS" as "Plant",
"MTRLNO" as "MaterialNo",
"ERDAT" as "CreatedOnDate", 
//Date(Date#("ERDAT",'YYYYMMDD'),'MM/DD/YYYY') as "NewCreatedOnDate", 
Date(alt(Date#("ERDAT", 'YYYYMMDD')), 'DD/MM/YYYY') as "NewCreatedOnDate" 
From "_SYS_BIC"."QV" 
Group By "AUFNR",
"AUART",
"WERKS",
"MTRLNO",
"ERDAT";

However, I encountered an error when I trying to load the script as below:

SQL##f - SqlState: S1000, ErrorCode: 328, ErrorMsg: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;328 invalid name of function or procedure: DATE: line 24 col 3 (at pos 614)

Any Idea how can I resolve this?

Re: SQL error when converting the date format from yyyymmdd to dd/mm/yyyy

vamsee — Thu, 26 May 2022 20:25:26 GMT

Try using the same date scripts in the load statement instead of the Select statement

Date(Date#(ERDAT,'YYYYMMDD'),'MM/DD/YYYY') as CreatedOnDateNew,

Re: SQL error when converting the date format from yyyymmdd to dd/mm/yyyy

Lily — Tue, 31 May 2022 04:37:53 GMT

I tried your suggestions and they worked perfectly. Thanks @vamsee 😄

Load 
   *,
   Date(Date#(CreatedOnDate,'YYYYMMDD'),'MM/DD/YYYY') as NewCreatedOnDate;
SQL Select
   "AUFNR" as "OrderNo",
   "AUART" as "OrderType",
   "WERKS" as "Plant",
   "MTRLNO" as "MaterialNo",
   "ERDAT" as "CreatedOnDate"
From "_SYS_BIC"."QV" 
Group By "AUFNR",
   "AUART",
   "WERKS",
   "MTRLNO",
   "ERDAT";