TSQL - Appending text to a datetime in the SELECT gives unwanted order of operations

Question

I have a query:

SELECT TOP 3 
    Person.Name AS PersonName,
    YEAR(Person.DateBorn)/100 AS JustCenturyNumbers,
    Person.PersonNumber AS RestOfDateTimeNumbers,
    YEAR(Person.DateBorn)/100+Person.PersonNummer AS CompleteDateTimeNumber
FROM Person 

What I want is to append the RestOfDateTimeNumbers to JustCenturyNumbers and get a complete datetime number representation like:

PersonName  JustCenturyNumbers  RestOfDateTimeNumbers   CompleteDateTimeNumber
Tim         20                  0003167012              200003160000                
Fred        19                  0009272725              190009270000                
Jenny       19                  0106207228              190106200000      

But what I get is:

PersonName  JustCenturyNumbers  RestOfDateTimeNumbers   CompleteDateTimeNumber
Tim         20                  0003167012              3167032
Fred        19                  0009272725              9272744
Jenny       19                  0106207228              106207247

I guess that the /100-function is somehow applied to the rest of the string somehow rather than the date alone, but I've been unable to solve it as I'm new to TSQL-functions.

Any idea of how I can fix this and get the result I want?

I use TSQL and SQL-Server 2008 (rt).

Answer 1

You're falling foul of Data Type Precedence when doing the concatenation. You need to CAST the first part of your formula to varchar if you want the final result to be treated as text rather than numeric:

SELECT TOP 3 
    Person.Name AS PersonName,
    YEAR(Person.DateBorn)/100 AS JustCenturyNumbers,
    Person.PersonNumber AS RestOfDateTimeNumbers,
    CAST(YEAR(Person.DateBorn)/100 AS varchar(2)) +Person.PersonNumber AS CompleteDateTimeNumber
FROM Person