Date Data Type changed automatically to VW_String when reading from SQL DB

I am writing a table to SQL DB. I have a columns which contains date. Before writing it tot the DB I made sure that the data type is set to Date for the column containing dates. But when I read the same table rom DB, it shows that the data type for the column containing date values is set to be VW_String instead of Date. As far as I know that Alteryx is able to extract right data types from databases and for other data types it does but not for the Date data type. Can anyone help in this regard?

Input

Database Connection

Accepted answers

jrgo

Hi @amitupadhyay

I think the reason is because of the default date format Oracle uses. If it's still the default, dates are stored as '31-DEC-21' which translates to '2021-12-31' (or the ISO 8601 format). I can't say if the issue is with the OCI/ODBC driver or in Alteryx, but if the date comes out in the proper ISO format, I think you'll have to just update the data type manually when read into Alteryx (Select tool).

Another option you can play with is by executing the below as a Pre-SQL statement configured in your Input tool. This would update the format for your session only,

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Hope this helps!

Jimmy

All comments

jrgo

Hi @amitupadhyay

The table you're writing/reading from may have that date column set as a STRING (or one of the variants) data type. Even though you've set the column to a DATE in Alteryx, the target SQL table may still accept it even if it's not set as a date field.

I'd suggest confirming that the date column in your DB table is properly set as a date data type. If it is, what SQL platform are you using (MSSQL, Oracle, etc...)?

-Jimmy

amitupadhyay

Hello Jimmy,

Sorry for the late response. I looked into the DB there it correctly shows it to be a Date Data Type. We are using Oracle SQL platform. But when reading into the workflow from the DB it shown VW_String.

jrgo

Hi @amitupadhyay

Another option you can play with is by executing the below as a Pre-SQL statement configured in your Input tool. This would update the format for your session only,

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Hope this helps!

Jimmy

amitupadhyay

Thank you Jimmy for providing a deeper insight into the Oracle DB date formatting. I did the same as per your suggestion. I added a select tool right after reading from the DB to modify to Date data type.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703