I'm importing a .csv file into SQL Server 2008. I did a "staging" import setting all fields to varchar(255) to see what data they contained. Next I ran an SSIS Data Profiling Task (thanks Barry) and got the Column Length Distribution profile.
The max length on most of the date fields is 21 (as expected because the data has double-quote text qualifiers around all of the values); however, some of the date fields have a max length greater than 21.
When I look for records where the maximum length exceeds 21 I get results but I don't see what I would expect in the actual data (additional characters).
For instance, if I run this:
select top 100 * from MyTable where len(rtrim(ltrim(MY_DATE1)))='28'
I get results like:
which looks like 21 characters to me.
I think I'm missing some foundational concept here. :)
Can anyone help me out?