You can translate the question and the replies:

formatting date to ddMMMyyyy which is equivalent to date9

Good day I have a datetime format represented as 07DEC2005:00:00:00.000000 please note this is a date not character. I now want to convert to 'ddMMMyyyy' but still to be in a date format not as text or character. i have tried this sql code formatdate('ddMMMyyyy', open_date) AS OPEN_DATE but this returns a string not sas date numeric for example date9 format. i have look at many example but they don't show pictures on how initially the data looked like. Please allow us to share screenshots. regards N
user
16-07-2020 09:22:04 -0400

1 Answer

Hi, In general, I would use the [FORMATDATE](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#formatdate) function in Virtual DataPort to display the date values in the desired format. This function always returns a string value. In order to convert the text value containing a date in a specific format, into a value of type date, I would use [to_localdate](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#to-localdate) function. For example, > SELECT TO_DATE(‘M dd yyyy HH:mm:ss’, ‘3 05 2010 21:17:05’) > FROM Dual(); Alternatively, you can use the [Cast](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/type_conversion_functions#cast) function to convert data from one data type to another. Additionally, If you want to retrieve the results of the date field in the desired format as a date type, I would recommend you to change the i18n code of your view by navigating to the *view > Options > Search methods > Default i18n*. You can define an own i18n map and set the date pattern you want to have for the date type. For more information, you can refer to the documents [Managing Internationalization Configurations](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/advanced_characteristics/creating_new_internationalization_configurations/creating_new_internationalization_configurations#managing-internationalization-configurations) and [Datetime Processing Functions](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#datetime-processing-functions) of the Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
17-07-2020 07:56:14 -0400
You must sign in to add an answer. If you do not have an account, you can register here