How can i combine a Text and Date field in a Join statement

[tbeenders]

I'm trying to compare a combined string and date field in a join statement. But it gives me a syntax error. What am i doing wrong?

InterPolisnummer = Text,  Ingangsdatum = Date

LEFT OUTER JOIN PUB.DekkingLeeftijd dklt
ON DKL.InternPolisnummer + convert(varchar, DKL.Ingangsdatum, 101) = DKLT.InternPolisnummer + convert(varchar, DKLT.Ingangsdatum, 101)

[robert_mika]

In sql that would probably work with concat()

In qlik scrippting with &

[tbeenders]

In SQL that will be like?

I have tried 2 options. First one give me another syntax error, 2nd one a Inconsistent type error.

ON concat(DKL.InternPolisnummer, convert(varchar, DKL.Ingangsdatum, 101)) = concat(DKLT.InternPolisnummer, convert(varchar, DKLT.Ingangsdatum, 101))

Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "varchar, DKL.Ingangsdatum, 101)) = conca" (10713)

ON concat(DKL.InternPolisnummer, DKL.Ingangsdatum) = concat(DKLT.InternPolisnummer, DKLT.Ingangsdatum)

Connector reply error: SQL##f - SqlState: S1000, ErrorCode: 4294947288, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Inconsistent types (7481)

[tbeenders · Accepted solution]

I found something on CoPilot:

ON CONCAT(DKL.InternPolisnummer, TO_CHAR(DKL.Ingangsdatum, 'MM/DD/YYYY')) = CONCAT(DKLT.InternPolisnummer, TO_CHAR(DKLT.Ingangsdatum, 'MM/DD/YYYY'))

Thx for your input!

[SunilChauhan]

try with two conditon  with and 
ON DKL.InternPolisnummer  = DKLT.InternPolisnummer  and 
 convert(varchar, DKL.Ingangsdatum, 101)= convert(varchar, DKLT.Ingangsdatum, 101)