Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
gfisch13
Creator II
Creator II

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

Labels (3)
1 Solution

Accepted Solutions
peterwh
Creator
Creator

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

View solution in original post

3 Replies
peterwh
Creator
Creator

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

View solution in original post

Kush
MVP
MVP

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
Creator II
Creator II
Author

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.