You can translate the question and the replies:

Need to convert an incoming date value of type Text to a type Date

I have a field called EFF_DATE in a Base View which of type TEXT which may or may not have a value in the field. If there is a value, it is in the format of 'yyyyMMdd'. I am creating a "Selection" Derived View which states that: If the incoming value of the EFF_DATE field is NULL put the value of '12/31/9999' in the Derived View. The column EFF_DATE in the Derived View will be of type DATE. Thus I need to convert the values from the Base View (type TEXT) to the Derived View (type DATE). Thus: (1) In the Base View, if there is no value of type TEXT (the Base View field value is NULL) --> I want to put a value of '12/31/9999' of type DATE in the Derived View. (2) If there is a value for the field EFF_DATE in the Base View field (of type TEXT in the 'yyyyMMdd' format --> I want to convert that value to format MM/dd/yyyy of type DATE in the Derived View. I am trying to use the FORMATDATE and TO_DATE functions for the conversion, however that is not working for me. Please let me know what the conversion function should be.
Date Conversion dateformat
user
10-03-2017 08:16:34 -0500
code

1 Answer

Hi, I tried the following steps and it worked for me, 1. In the derived view, I added a new field with condition. ``` CASE WHEN (bv_dates.eff_date is null ) THEN to_date('MM/dd/yyyy', '12/31/9999') ELSE to_date('yyyyMMdd', bv_dates.eff_date) END. ``` 2. Created a new I18N with date pattern 'MM/dd/yyyy'. 3. In Virtual Dataport, Tools > Admin Tool preferences > Locale. selected the created I18N and enabled the check box “Internationalize query results” as the results of a query will be formatted with locale and time pattern configured with the OS. 4. Alternatively, you can also specify the created I18N in CONTEXT clause. For example: ``` SELECT * FROM dateresults WHERE CONTEXT ('i18n'=<New I18N>) ``` You can also refer the section “Locale” and “Internationalization Configuration” in [Administration Guide](https://community.denodo.com/docs/view/document/Denodo%20Express/Denodo%206.0/Virtual%20DataPort%20Administration%20Guide). Hope this helps.
Denodo Team
13-03-2017 08:54:06 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here