topic Re: Converting a DATETIME field to DATE from SQL Source in QlikView

Converting a DATETIME field to DATE from SQL Source

gfisch13 — Sat, 16 Nov 2024 00:06:28 GMT

Hi folks,

I'm trying to convert a DATETIME field to DATE. The field I'm loading comes from and SQL Source using an SQL SELECT format in my script.

I've read several posts on this site and used a function that I thought would work but when I reload I'm getting an error that states 'date' is not a recognized SQL function. I'm open to learning any new ways to accomplish this task! Script Below:

VenMaster:
SQL SELECT "ACT_REC_IND",
"ADR_SEQ_NUM",
// "AUDT_ID",
"BNK_SET_A_ID",
"DATA_SRC_CD",
"EFF_DT",
Date(Date(Left("EFF_DT",10),'MM/DD/YYYY')) as Date,
// "ETL_CYC_DT",
// "ETL_UPDT_DT",
// "ETL_XTRCT_DT",
"VND_ADR_1_TXT",
"VND_ADR_2_TXT",
"VND_ADR_3_TXT",
"VND_ADR_4_TXT",
"VND_CITY_NM",
"VND_CLASS_CD",
"VND_DBA_NM",
"VND_ID",
"VND_NM",
"VND_PST_CD",
"VND_ADR_1_TXT" + ' ' + "VND_PST_CD" as AddressMatch,
"VND_ST_CD",
"VND_STTS_CD"
FROM "DMBA_SIU".dbo."T_APRV_VND_DTL_PRST";

Re: Converting a DATETIME field to DATE from SQL Source

peterwh — Fri, 21 Aug 2020 06:41:36 GMT

Try to use a preceding load. This way you get your data through SQL and can transform it with QlikView-Syntax and there's no need to use database specific SQL.

VenMaster:
LOAD
*,
Date(Floor(EFF_DT) AS EFF_Date
;
SQL SELECT "ACT_REC_IND",
"ADR_SEQ_NUM",
// "AUDT_ID",
"BNK_SET_A_ID",
"DATA_SRC_CD",
"EFF_DT",
// "ETL_CYC_DT",
// "ETL_UPDT_DT",
// "ETL_XTRCT_DT",
"VND_ADR_1_TXT",
"VND_ADR_2_TXT",
"VND_ADR_3_TXT",
"VND_ADR_4_TXT",
"VND_CITY_NM",
"VND_CLASS_CD",
"VND_DBA_NM",
"VND_ID",
"VND_NM",
"VND_PST_CD",
"VND_ADR_1_TXT" + ' ' + "VND_PST_CD" as AddressMatch,
"VND_ST_CD",
"VND_STTS_CD"
FROM "DMBA_SIU".dbo."T_APRV_VND_DTL_PRST";

Explanation of the transformation (if EFF_DAT is a timestamp field)
Floor(...) => get rid of the hours, minutes,..., else you have in a listbox the date multiple times
Date(...) => convert into a QlikView-Date

Kind regards

Peter

Re: Converting a DATETIME field to DATE from SQL Source

Kushal_Chawda — Thu, 20 Aug 2020 20:52:16 GMT

You are using Qlik native functions in SQL statement which will not work. You need to use functions which is supported by your source i.w native to Sql source. You can try something like below

VenMaster:
SQL SELECT "ACT_REC_IND",
"ADR_SEQ_NUM",
// "AUDT_ID",
"BNK_SET_A_ID",
"DATA_SRC_CD",
"EFF_DT",
CONVERT("EFF_DT", getdate(), 101) as DATE,
// "ETL_CYC_DT",
// "ETL_UPDT_DT",
// "ETL_XTRCT_DT",
"VND_ADR_1_TXT",
"VND_ADR_2_TXT",
"VND_ADR_3_TXT",
"VND_ADR_4_TXT",
"VND_CITY_NM",
"VND_CLASS_CD",
"VND_DBA_NM",
"VND_ID",
"VND_NM",
"VND_PST_CD",
"VND_ADR_1_TXT" + ' ' + "VND_PST_CD" as AddressMatch,
"VND_ST_CD",
"VND_STTS_CD"
FROM "DMBA_SIU".dbo."T_APRV_VND_DTL_PRST";

Re: Converting a DATETIME field to DATE from SQL Source

gfisch13 — Fri, 21 Aug 2020 12:44:40 GMT

The preceding load worked better in this situation as my weakness with SQL did not allow me to address some errors that appeared.

Errors were mainly syntax related.

Thank you both for your contributions and learning experience.