You can translate the question and the replies:

How to convert a date and time to another format?

Hi! I would like to know how to convert a column with a date format such as dd/MM/YYYY 12:00:00 a.m to a format where only shows "dd/MM/YYYY". I have used the function FORMATDATE('dd/MM/YYYY, datecolumn) and although it gives me the format that I want the problem is that it gives me back a String so, when I try to use and ORDER clause in order to have the information by the date its not possible. This because is in a String format. Also I would like to know how to do the same with a time column with the values like this "HH:mm:ss" to convert it to "HH:mm" without affecting the properties of the column replacing it with a string. This because I also want to order by the time. I have tried to use first a FORMATDATE clause on both (date and time) in order to convert them to the format that I want and after that I have used a “TO_LOCALDATE” and “To_Time” clause in order to give them back their original properties but after this I have the same result as the beginning a date with the format “dd/MM/YYYY 12:00:00 a.m” and a time with the format "HH:mm:ss" but having zeros on the seconds. I would appreciate a response. Thanks.
user
08-09-2020 17:56:01 -0400

1 Answer

Hi, In Denodo7, the datetime values are always displayed with the same format, regardless of the setting or language setting of the computer where Virtual Dataport administration tool runs. Hence the default format for the type localdate, timestamp,timestamptz are the same. The date part will be always displayed as ‘yyyy-MM-dd’ and the time part will be always displayed as “HH:mm:ss.S”.So, if you want to display the datetime in different format then use FORMATDATE function. For more information, refer to the [Changes in Date Types](https://community.denodo.com/docs/html/browse/7.0/platform/migration/backward_compatibility/changes_in_the_denodo_platform_7.0/changes_in_virtual_dataport#changes-in-date-types) section of the Denodo Platform Migration Guide. In order to achieve your requirement, I would follow the below steps. 1. Delegate the ORDER BY clause to the datasource during base view creation using the “[Create from query](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries)” option. By the way I would make sure my base view output is always sorted based on the date field. 1. Create a derived view over the base view and modify the date field to use FORMATDATE function on the field expression to convert the output in the desired format(i.e. dd/MM/yyyy). 1. Now when you execute the derived view, it will return the date field in the desired format with sorted output. For the case, where ORDER BY is not able to delegate to the datasource, then i would follow the below steps. 1. Create a base view using the introspection option. 1. Create a derived view over the base view and add a [new derived field](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_union_views/creating_union_views#:~:text=Add%20derived%20attributes) to use FORMATDATE function on the field expression for the new field. 1. In the Output tab of the derived view, choose the one or more fields (Not the field created in step 2) in the [ORDER BY](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/creating_union_views/creating_union_views#:~:text=Order%20the%20output%20by%20one%20or%20more%20fields) fields dropdown and click (+) icon to define the sorting order. 1. Now when you execute the derived view, the returned result set is sorted along with desired format. For more information, you can refer to the [Datetime Processing Functions](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions) section of the Virtual DataPort VQL Guide. If still see the issues persist and If you are a user with valid support access then you can raise a support case in [Denodo Support Site](https://support.denodo.com/) so that our support team can help you. Hope this helps!
Denodo Team
09-09-2020 05:51:13 -0400
You must sign in to add an answer. If you do not have an account, you can register here