You can translate the question and the replies:

Getday/months function mask to two digits number

Hey denodo team, Is it possible to get a two digits number for those months that are lower to 10? I'm using the getday and getmonth functions to join both date fields (because they are on different times and I only want the date information). We use that functions because the trunc function is not being pushed to the source. The problem comes in when the concated string is being the same for different dates. For example: Tue Nov 01 00:00:00 EDT 2011 = 2011 11 1 Tue Jan 11 00:00:00 EST 2011 = 2011 1 11 When I concatenate using the getday/months, those 2 dates are being mixed up. I should get this: Tue Nov 01 00:00:00 EDT 2011 = 2011 11 01 Tue Jan 11 00:00:00 EST 2011 = 2011 01 11 Can I explain my issue? Any thoughts? Any workarounds? Thanks in advance! Regards, Fabian
Date Conversion getday Data Mask
user
12-10-2017 16:20:13 -0400
code

3 Answers

Hi, In order to get two digits for the day/month that are less than 10, I would use the ‘FORMATDATE’ function which converts the ‘date’ type value into ‘string’ type with the specified format by using the following syntax: * FORMATDATE('yyyy MM dd', <date_field>) For more information, you can have a look at the section [FORMATDATE](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions#formatdate) of Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
13-10-2017 05:23:54 -0400
code
Is that function pushed to the source? Or, does it depend on the source?
user
 Edited on: 13-10-2017 10:51:10 -0400
Hi, The FORMATDATE function will be pushed down to the JDBC/ODBC data sources if this function is included in the scalar functions list of “Source Configuration” tab. If a view or a query uses the function FORMATDATE and this function is delegated to a database, you should only use a date pattern (parameter date_pattern) supported by the database. Otherwise, the query will fail. If you use a date pattern which is not supported by the data source, then you can avoid delegating the function ‘FORMATDATE’ to the data source. To do this, remove the FORMATDATE function from the “Delegate Scalar Functions List” of the “Source configuration” tab of the JDBC/ODBC data sources. For more information you can have a look at the section [Data Source Configuration Properties](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/data_source_configuration_properties) of Virtual DataPort Administration Guide and and Knowledge Base Article [Scalar functions list in a data source](https://community.denodo.com/kb/view/document/Scalar%20functions%20list%20in%20a%20data%20source?category=Data+Sources). Hope this helps!
Denodo Team
23-10-2017 07:05:19 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here