How to retrieve DATE data type from the TIMESTAMP data type column using TRUNC function in TIBCO Data Virtualization?
Article ID: KB0075002
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | All Supported Versions |
Description
When the below query is executed from the TDV Studio, the TRUNC function returns the result in the format "2020-06-30 00:00:00" instead of "2020-06-30". Here column "COL2" is of the data type TIMESTAMP.
SELECT
TIME_TEST.COL1 "SR No",
trunc(TIME_TEST.COL2) "Entry Time"
FROM
/shared/Oracle12c_DB/TEST/TIME_TEST TIME_TEST
Issue/Introduction
How to retrieve DATE data type from TIMESTAMP data type column using TRUNC function in TIBCO Data Virtualization?
Environment
All supported Operating Systems
Resolution
Follow the below steps to achieve the use case:
1. Open TDV Studio.
2. Navigate to Administration-->Configuration-->Server-->SQL Engine-->Overrides-->TRUNC and TRUNCATE Functions Always Return DATE Data Type
The default value for this configuration is set to FALSE. Change this value to TRUE.
3. Apply the changes and close the configuration window.
4. Now open the view which contains TRUNC function, perform re-save on this view.
NOTE: Re-saving the view is a mandatory step after changing the above configuration as it will take effect only after re-saving the procedure or view which is using it.
5. Now execute the view having TRUNC function and check if the result contains date in format "YYYY-MM-DD".
1. Open TDV Studio.
2. Navigate to Administration-->Configuration-->Server-->SQL Engine-->Overrides-->TRUNC and TRUNCATE Functions Always Return DATE Data Type
The default value for this configuration is set to FALSE. Change this value to TRUE.
3. Apply the changes and close the configuration window.
4. Now open the view which contains TRUNC function, perform re-save on this view.
NOTE: Re-saving the view is a mandatory step after changing the above configuration as it will take effect only after re-saving the procedure or view which is using it.
5. Now execute the view having TRUNC function and check if the result contains date in format "YYYY-MM-DD".
Feedback
Yes
No
Powered by
