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

[amitupadhyay]

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?

[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 · Accepted solution]

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

[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.