My source is a sequential file and I am reading date columns as Varchar(10). My problem is the date fields contains \N character.
I want these dates as Null so using the below logic but still logic is not working as it is supposed to work.
Just to be sure, are you saying that few of your valid dates in the given date column are followed by a newline character? e.g. 2012-09-09\n
And you want all such VALID dates followed by a newline to be set to null?
It looks more like your blank dates have a new-line character in which case your logic should be correct.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
This might sound childish but when you say \N, you mean the newline character '\n' right? In my opinion the \n is where it should be. It's a new-line/record delimiter. Your logic might be failing because the new-line character is not a part of the column's data!
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
Seriously? So not a new line character at all but literally two characters - a slash followed by an N. And if that's the case there's absolutely no need for the Convert() as a simple if-then-else would suffice.
-craig
"You can never have too many knives" -- Logan Nine Fingers
And - strange enough - this is what the OP reported as not working in the beginning. If wie ignore the trim()-function around the date-field, that is...
Nobody ever asked what the result of the original derivation was. Or which error message was received. There must have been a misspelling or some minor mistake, because the logic should be generally correct.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon