Dynamic / conditional SQL join?

Question

I have data in an MSSQL table (TableB), where [dbo] .tableB.myColumn changes the format after a certain date ...

I make it simple to join this table.

Select [dbo].tableB.theColumnINeed from [dbo].tableA 
left outer join [dbo].tableB on [dbo].tableA.myColumn = [dbo].tableB.myColumn

However, I need to join using a different formatting based on the date column in table A ([dbo] .tableA.myDateColumn).

Sort of...

Select [dbo].tableB.theColumnINeed from [dbo].tableA 
left outer join [dbo].tableB on [dbo].tableA.myColumn = 
    IF [dbo].tableA.myDateColumn > '1/1/2009'
        BEGIN
            FormatColumnOneWay([dbo].tableB.myColumn)
        END
    ELSE
        BEGIN
            FormatColumnAnotherWay([dbo].tableB.myColumn)
        END

I am wondering if there is a way to do this .. or a better way that I don’t think about in order to approach this.

Answer 1

SELECT [dbo].tableB.theColumnINeed
FROM   [dbo].tableA 
LEFT OUTER JOIN [dbo].tableB
ON [dbo].tableA.myColumn = 
   CASE
    WHEN [dbo].tableA.myDateColumn <= '1/1/2009' THEN FormatColumnOneWay([dbo].tableB.myColumn)
    ELSE FormatColumnAnotherWay([dbo].tableB.myColumn)
   END

Answer 2

Instead of having a CASE statement in JOIN that will prevent queries using indexes, you might consider using UNION

SELECT [dbo].tableB.theColumnINeed 
FROM   [dbo].tableA 
    LEFT OUTER JOIN [dbo].tableB 
         ON [dbo].tableA.myDateColumn > '1/1/2009'
        AND [dbo].tableA.myColumn = FormatColumnOneWay([dbo].tableB.myColumn)
UNION ALL
SELECT [dbo].tableB.theColumnINeed 
FROM   [dbo].tableA 
    LEFT OUTER JOIN [dbo].tableB 
         ON [dbo].tableA.myDateColumn <= '1/1/2009'
        AND [dbo].tableA.myColumn = FormatColumnAnotherWay([dbo].tableB.myColumn)

FormatColumnOneWay/FormatColumnAnotherWay - , , , inxdexes [myColumn], myDateColumn

, FormatColumnOneWay/FormatColumnAnotherWay, ,

:

UNION ALL ( UNION). , SORT, UNION , .

"1/1/2009" , "yyyymmdd" ( CONVERT , , d/m/y m/d/y style

Answer 3

[dbo], , SQL Server. , :

select * from tableA
  Left Outer join tableB
       On CONVERT(CHAR(8), tableA.myColumn, 112) = CONVERT(CHAR(8), tableB.myColumn, 112)

, .

SQL Server, Oracle .

Answer 4

SQL Server CASE, :

SELECT * 
FROM TableA
INNER JOIN TableB on TableA.Column=
CASE WHEN TableA.RecordDate>'1/2/08'
       THEN FormatCoumn(TableB.Column) 
     ELSE FormatColumnOtherWat(TableB.Column)
END

Answer 5

, , ?

kludge CASE ... , ,

Answer 6

, , .

SELECT
  newB.columnINeed
FROM
  tableA AS A
LEFT OUTER JOIN (
  SELECT
    columnINeed
  , CASE WHEN myColumn > '1/1/2009' THEN FormatColumnOneWay(myColumn)
    ELSE FormatColumnAnotherWay(myColumn)
    END AS myColumn
  FROM
    tableB
) AS NewB ON A.myColumn = B.myColumn

, ( ) .

Answer 7

, CASE , , .

"" , , , , . datetime, , , B datetime. , :

• dummie TableB datetime.
• , datetime.
• .

Answer 8

, . ? , DateTime, ... . CAST CONVERTED DateTime?

,

left outer join tableb on tableA.myColumn = CAST(tableb.MyColumn as DateTime)

, , , . . - DateTime ...