T-SQL | DATENAME

DATENAME('date_part', date_field)

Например, для связки дат в несовместимых форматах (в примере связываются даты из TABLE_NAME_1 и TABLE_NAME_2):

SELECT

TABLE_NAME_1.FIELD_1,

TABLE_NAME_1.DATE AS LAST_ORDER_DATE,

...

FROM TABLE_NAME_1

INNER JOIN (SELECT MAX(DATE) AS DATE FROM TABLE_NAME_2) LAST_ORDER

ON

DATENAME(YEAR, TABLE_NAME_1.DATE) = DATENAME(YEAR, LAST_ORDER.DATE) AND

DATENAME(MONTH, TABLE_NAME_1.DATE) = DATENAME(MONTH, LAST_ORDER.DATE) AND

DATENAME(DAY,   TABLE_NAME_1.DATE) = DATENAME(DAY,   LAST_ORDER.DATE)