I have a varchar column in an oracle database that stores date in the following format:
18-000010
I am attempting to use some automation to pick up this column and a) insert it to another table or b) output to an email message. However, whenever the field is accessed as a variable in toad, it parses the column values out as math. So, for the example above, the column is reduced to “8” - because 18 - 10 = 8.
I have tried to force toad to treat it as text by forcing it in the sql to append single quotes (SELECT CONCAT(CONCAT(’’’’,DATA_COLUMN),’’’’) AS DATA_COLUMN, which works in some cases, but then i ran into a new problem. Toad parsed out:
‘11-003066’
as
01-NOV-66 12.00.00.000000000 AM
I feel like I must be missing something in how to handle this field, but I can’t figure it out. Toad will handle the field fine if there are any letters in the data (ABC11-003066, for example), but the other fields have very unexpected results. Any tips or pointers on how to handle varchar data in this format when passing it through Toad variables?
In this particular case Automation works correctly b/c there is no information about varchar value of ‘18-000010’ is actually a date. Automation makes a decision about such variable type based on column type. In this case treating such string as a math expression is the best guess possible.
To solve the problem I would suggest converting the column in question to a date column by adding additional calculated column in SQL. Automation then should work with this calculated column of date type.
Thank you, but it is never the case that the column data is a date. It should always be passed through as a string, unaltered. Toad alters it in unexpected ways, sometimes parsing as math, other times trying to interpret a date - but I want that to never happen.
I want
18-000010 and 11-003066
to always pass through unaltered. And I am struggling to understand how to stop Toad from altering it.