CODEWITHSUNDEEP
sqlteradata
The Nerge
The Nerge

Reputation: 137

character_length Teradata SQL Assistant

I have to run column checks for data consistency and the only thing that is throwing off my code is checking for character lengths for dates between certain parameters.

SEL
sum(case when ( A.date is  null or (character_length(A.date) >8)) then 1 else 0 end ) as Date
from
table A
;

The date format of the column is YYYY-MM-DD, and the type is DA. When I run the script in SQL Assistant, I get an error 3580 "Illegal use of CHARACTERS, MCHARACTERS, or OCTET_LENGTH functions."

Preliminary research suggests that SQL Assistant has issues with the character_length function, but I don't know how to adjust the code to make it run.

Upvotes: 1

Views: 13866

Answers (1)

gbtimmon
gbtimmon

Reputation: 4332

with chareter length are you trying to get the memory used? Becuase if so that is constant for a date field. If you are trying to get the length of the string representation i think LENGTH(A.date) will suffice. Unfortanatly since teradata will pad zeros on conversions to string, I think this might always return 10.

UPDATE :

Okay so if you want a date in a special 'form' when you output it you need to select it properly. In teradata as with most DBs Date are not store in strings, but rather as ints, counting days from a given 'epoch' date for the database (for example the epoch might be 01/01/0000). Each date type in teradata has a format parameter, which places in the record header instructions on how to format the output on select. By default a date format is set to this DATE FROMAT 'MM/DD/YYYY' I believe. You can change that by casting.

Try SELECT cast(cast(A.date as DATE FORMAT 'MM-DD-YYYY') as CHAR(10)) FROM A. and see what happens. There should be no need to validate the form of the dates past a small sample to see if the format is correct. The second cast forces the database to perform the conversion and use the format header specified. Other wise what you might see is the database will pass the date in a date form to SQL Assitant and sql assitant will perform the conversion on the application level, using the format specified in its own setting rather then the one set in the database.

Upvotes: 2

Related Questions