Converting a DATETIME field to DATE from SQL Source

[gfisch13]

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

[peterwh · Accepted solution]

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

[Kushal_Chawda]

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

[gfisch13]

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.