

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Converting a DATETIME field to DATE from SQL Source
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";
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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";


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
