MSSQL: difference between servers regarding date conversion

Question

I have this script:

SELECT UserID, 
       CONVERT(SmallDateTime, cast([1]  + '/' + [2] + '/' + [3] as varchar)) as ReleaseDate, 
       ChannelID
FROM
(
SELECT RU.UserID, U.FieldID,  RU.ChannelID, U.AnswerText
FROM dbo.Users U WITH (NOLOCK) INNER JOIN dbo.ResourceUsers RU WITH (NOLOCK)
      ON U.UserID = RU.UserID
WHERE  (FieldID = 1 -- release day
          OR FieldID = 2 -- release month
          OR FieldID = 3) 
) AS P
PIVOT 
(
      MAX(AnswerText) FOR FieldID in ([1], [2], [3])
) AS pvt

The same script runs perfectly on a server. On other server, I got this error:

Msg 295, Level 16, State 3, Line 3 Conversion failed when converting character string to smalldatetime data type.

Why? What's the difference? The tables have the same structure on both servers.

Answer 1

In sql server 2012+ you can use datefromparts

SELECT UserID, 
       convert(smalldatetime,datefromparts([3],[2],[1])) as ReleaseDate, 
       ChannelID
FROM ....

Prior to sql server 2012 that you can create a string in 'yyyyMMdd' format

SELECT UserID, 
       convert(smalldatetime,
          convert(char(4),[3])
        + right('0'+convert(varchar(3),[2]),2)
        + right('0'+convert(varchar(3),[1]),2)
        ) as ReleaseDate, 
       ChannelID
FROM ....