You can translate the question and the replies:

Date Formatting

Hello, Here is my issue. I am working in Alteryx, pulling a table from Denodo. I'm looking to convert some odd date values to the equivalent format of Current_Date(). Like 2018-07-31 00:00:00. The date values I'm looking to convert are like such. 910101 is an example of the date value. This is equivalent to Jan 1, 1991. But 1391231 is another example of the date value and is equivalent to Dec 31, 2039. 1020528 would be equivalent to May 28, 2002. These date values are stored in Denodo as Numeric with a length of 7. I have tried messing around with FORMATDATE(), but without much success. Any advice as to how to properly convert these dates to the same format as Current_Date()? Thanks, Ryan
user
31-07-2018 11:07:09 -0400

4 Answers

Hi, So, it looks like you have CYYMMDD format dates, which aren’t supported as is in the [date processing functions](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/date_processing_functions) available in denodo. The C is for century, essentially translating CYY into 1900 + YYY = yyyy What you’re going to need to do is get the integer into something we can turn into a valid date, then format it afterwards. It’s actually pretty easy to do when you know what you’re working with, you just need to add 19000000 to your integer and it should process fine: to_date('yyyyMMdd', (19000000 + <your int>)) From here you can format it as needed. Hope this helps!
Denodo Team
02-08-2018 20:08:50 -0400
hI Ryan, are you able to connect Denodo to Alteryx? im facing challenges with Error: Output Data (5): Error creating table "TESTING.IND_Purchasing": ERROR: Syntax error: Exception parsing query near '.' java.sql.SQLException: Syntax error: Exception parsing query near '.'; Error while executing the query CREATE TABLE "TESTING"."IND_Purchasing" ("Supplier Name" varchar(35),"Supplier Id" varchar(10)) is it possible for you to connect with me -brijendra.yadav@te.com or if you can give me your email?
user
08-08-2018 11:47:29 -0400
Hey brijendra, I am able to connect to Denodo through Alteryx. What I do is go directly into the sql window through the input data tool. You use the same syntax as you would other tools connecting to Denodo. Same syntax as you find on this forum. However, sometimes values output differently. Just depends on your query and the input data. Are you able to show me the query in here? If I can look at the code I can probably help. Unfortunately it would be against my company policy to correspond through email. If you have a linkedin maybe we could correspond through that. Just post your linked in profile link.
user
08-08-2018 13:00:49 -0400
Hi Brijendra, From what I can see, you have a couple of syntax errors in your create statement. First off, the quotes around the parts of the table declaration are unnecessary. Denodo VQL shell also apparently doesn’t like fully qualified tables, at least in create statements. So, make sure you’re connected to the db that you want this table in when you run it, and remove “TESTING”, that will eliminate the syntax error near “.”. But you also can’t have spaces in column names, so you ought to replace those with underscores. After that, you should be good to go. I would test it with [CREATE TEMPORARY TABLE](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/temporary_tables/creating_temporary_tables/creating_temporary_tables) if you have cache configured, just to let you tweak it and play around with it without creating anything permanent. Hope this helps!
Denodo Team
21-08-2018 14:22:36 -0400
You must sign in to add an answer. If you do not have an account, you can register here