You can translate the question and the replies:

Date Format Transformation Pass Through Query in SAS

Hello, I have created a query in SAS that passes through Denodo via odbc connection (uses VQL syntax). My input date (effdate) is numeric with a length of 8 and looks like 20180815. I have tried numerous ways of converting it to a date. No matter what I do, it seems to output in SAS as a char. Ideally, I'd like it to output as a Date with the format of 15aug2018. Here are some things I have tried to no avail. Some of them will display like a date (2018-08-15), but don't return as actual dates, all of them return as chars. Not sure if this is an issue with SAS but I expected at least some of them to return as dates. formatdate('yyyy-MM-dd',to_date('yyyyMMdd',cast(effdate as varchar(8)))) as "EDate", to_date('yyyyMMdd',trim(cast(effdate as varchar(8)))) as "EDateTwo", to_date('yyyyMMdd',effdate) as "EDateThree", to_date('yyyy-MM-dd',trim(cast(effdate as varchar(8)))) as "EDateFour", to_date('yyyy-MM-dd',effdate) as "EDateFive", formatdate('yyyy-MM-dd',to_date('yyyyMMdd',effdate)) as "EDateSix", to_date('dd-MM-yyyy',effdate) as "EDateSeven", formatdate('dd/MM/yyyy', to_date('E MMM dd HH:mm:ss Z yyyy', effdate)) as "EDateEight" Thanks
15-08-2018 14:12:49 -0400

3 Answers

Hi, First things first, formatdate returns a string, which, unless cast to a date, or passed into to_date, will still be a string. You also don’t need to cast effdate to varchar for any of these, they will accept the numeric value if they can be a date. One last thing, the date formatting used in to_date is the format the date is in, so that it can recognize the date pattern, not the format you want it to be in. Since to_date gets us the date format, and you’re saying that SAS is importing this as a char. So, rather than trying to finagle out the inter-system conversions, my first recommendation would be to get a date in denodo formatted in a way that SAS conversions will accept, and do the conversion in SAS. A quick google search for SAS date varchar gives input(EDate,date9.); date9 apparently should be the same as a denodo ddMMMyyyy, so, I would do a formatdate around a to_date, and if necessary to convert in SAS, wrap that in a lower() function, and then convert it in SAS so it has date type. Hope this helps!
Denodo Team
17-08-2018 17:17:29 -0400
This definitely helps, I appreciate it. Thanks for the detail about the date functions.
20-08-2018 13:22:11 -0400
No problem, happy to help :)
Denodo Team
22-08-2018 12:01:56 -0400
You must sign in to add an answer. If you do not have an account, you can register here