New Line in Date Field

[chandra.shekhar@tcs.com]

Hi,

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.

Code: Select all

If TRIM(Lnk_Src_To_Trf.EXTRNL_RTNG_STRT_DT) = '\N' Then SetNull() Else
 StringToDate(Lnk_Src_To_Trf.EXTRNL_RTNG_STRT_DT,"%yyyy-%mm-%dd")

[jerome_rajan]

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.

[chandra.shekhar@tcs.com]

My Data looks like

Code: Select all

ID|Date
1|2009-12-12
2|2012-01-01
3|\N
4|\N

Here for ID = 3 and 4, I want date as Null.
But above logic is not working correctly.

[jerome_rajan]

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_rajan]

Try

Code: Select all

If TRIM(Lnk_Src_To_Trf.EXTRNL_RTNG_STRT_DT," ","A") = '' Then SetNull() Else 
 StringToDate(Lnk_Src_To_Trf.EXTRNL_RTNG_STRT_DT,"%yyyy-%mm-%dd")

[BI-RMA]

To identify a special-character like '\n' you had better check for the ascii-value of this character.
Try

Code: Select all

If Lnk_Src_To_Trf.EXTRNL_RTNG_STRT_DT = char(10) then SetNull() Else 
 StringToDate(Lnk_Src_To_Trf.EXTRNL_RTNG_STRT_DT,"%yyyy-%mm-%dd")

[srinivas.g]

Use the below expresion in Stage variable
Convert('\N','XX',<<Column_name>>) --> S1

Derivation use the below logic :
if s1='XX' then setnull() else stringtodate(s1,"%yyyy-%mm-%dd")

[chandra.shekhar@tcs.com]

@Srini
The logic worked. Thanx

[chulett]

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.

[BI-RMA]

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.

[jerome_rajan]

So my question was not childish after all

This might sound childish but when you say \N, you mean the newline character '\n' right?