Remove timestamp in date column

Hi All, I have a table\view in denodo with column as "createddate", below was the date format I am getting from this column. Tue Apr 21 13:53:52 EDT 2015 Tue Apr 21 13:22:12 EDT 2015 but i dont want time stamp and need in the below format. 2015-04-21 or 04-21-2015 21-04-2015 I tried to_date, truncate... but it does not working. Please help me once. Thanks, Saidi.
user
24-10-2017 17:17:40 -0400

3 Answers

Hi, I performed the following steps to retrieve the results only with date values in Virtual DataPort Administration Tool and it worked fine for me, * Go to the Edit tab of Base view. * Click on the pencil icon of the date field. * In Source type properties dialog box, choose "DATE". For the DATE to be displayed in desired format, you could create a custom i18n Map with required date pattern and assign it to the base view. I would suggest you to refer the answer of [date formate](https://community.denodo.com/answers/question/details?questionId=9060g0000004EhLAAU&title=date+formate) community question for more information on it. You could refer [Viewing the Schema of a Base View](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/viewing_the_schema_of_a_base_view) section of Virtual DataPort Administration Guide to know about Source type properties. Hope this helps !!
Denodo Team
25-10-2017 07:35:16 -0400
Thank you for your support, I went my base view->lick on Edit->i see my column have date format only, i clicked pencil there also I am seeing Type: DATE..but column output level..getting with timestamp. Tue Apr 21 13:53:52 EDT 2015 Wed Jun 03 14:23:26 EDT 2015 Wed Jun 10 15:35:35 EDT 2015 --but I did onething in the dervied view level. cast('text', `APPLICATIONSTATUS_my_testing_remove`.`CREATEDATE`) data coming in the below format: 21-APR-15 03-JUN-15 10-JUN-15 if we use this column as a paramter, we have to give the correct format..anotherwise its not working. instead of changing i18n, is there any way to covert into below format. 21-APR-2015 or 21-apr-2015 Please let me now that would be grate support. Thanks, Saidi.
user
25-10-2017 10:27:00 -0400
Hi, When you execute queries using Virtual DataPort Administration Tool, the "Internationalize query results" option internationalizes the result of the queries based on the i18n chosen in Locale. To retrieve the Date without TIMESTAMP, you need to enable 'Internationalize query results' by navigating to Tools > Admin Tool Preferences > Locale and choose the custom i18n. You could use FORMATDATE function, that converts the DATE as a text value in desired format. The syntax of FORMATDATE is, `FORMATDATE('dd-MMM-yyyy',<datefield>)` You could refer [Locale](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/installation_and_execution/launching_the_virtual_dataport_administration_tool/tool_preferences#locale) section of Virtual DataPort Administration Guide to know more about Internationalization and [Date Processing Functions](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#formatdate) section of Virtual DataPort VQL Guide to know more about FORMATDATE function. Hope this helps !!
Denodo Team
01-11-2017 08:11:36 -0400
You must sign in to add an answer. If you do not have an account, you can register here