Invalid date - datatype declared as varchar

General
Enthusiast

Invalid date - datatype declared as varchar

Hi Experts,

There was an issue once as the job has failed with Invalid date issue.  On further checking, found that the date/timestamp column was declared of the datatype as

VARCHAR(24) CHARACTER SET UNICODE NOT CASESPECIFIC

in staging table. 

1. As the no. of digits in the column is fixed as 8, why are we using 24 which is overhead (I accept that only 8 will be used and the remaining will be free, it could be declared as varchar(8))

2. Using varchar has an overhead of 2 extra bytes to store the length.  Having known the length of the data in the column will be 8 , we are additionally using 2 extra bytes to store for each record.

There should be some kind of performance gains that outperforms the above ones. Can you explain elucidate it. Looking forward to the answers!

1 REPLY
Junior Contributor

Re: Invalid date - datatype declared as varchar

Hi Cheeli,

the "invalid date" was not caused by the wrong datatype :-)

But of course this oversized varchar is wrong (maybe it was originally based on an Oracle DATE), if it's always 8 chars best fit would be a CHAR(8) CHARACTER SET LATIN.

Dieter