Trigger date conversion fails

Question

I have a trigger doing an INSERT_AFTER to a different table. The destination table is a staging table for data to be sent to another company. In the database on our side, the date is a smalldatetime. The other comp;any needs this split to a Date field and a Time field. Neither make it to the staging table. I have tried several different things including CAST and CONVERT with no success.

The pertinent SQL is below:

    CAST(inserted.CallInDate AS DATE)   AS ClientCallinDate,
    CAST(inserted.CallInDate AS TIME)   AS ClientCallinTime,
    --CONVERT(DATE,inserted.CallInDate) AS ClientCallinDate,
    --CONVERT(TIME,inserted.CallInDate) AS ClientCallinTime,

This trigger is following another INSERT_AFTER trigger doing the same thing to other tables. The first trigger is fired as a "First" and the trigger with the problem is fired as a last.

`EXEC sp_settriggerorder @triggername=N'[dbo].[TR_FirstTable_to_Client_I]', @order=N'First', @stmttype=N'INSERT'`

The second trigger also has another field failing that is created by the first trigger as a confirmation of receipt from the other company. II do not think these are related, but seeing as I have not figured either out, I cannot be sure.

`EXEC sp_settriggerorder @triggername=N'[dbo].[TR_FirstTable_to_Client_II]', @order=N'Last', @stmttype=N'INSERT'`

What I need is knowledge of what could be failing or what in SQL I need to change. I have dropped the trigger and recreated it, but that was of no help, and actually gave me an error I have yet to solve.

Answer 1

If your code above is part of an INSERT statement, it might just be failing because of the aliasing: AS ClientCallinDate. As long as your statement is constructed correctly, you do not need to alias the columns in the SELECT statement.