SQL Server too happy about adding the day and month

Question

I'm starting with this query.

select 
  count(datepart(day, SomeTime)) as NumberOf, 
  datepart(day, SomeTime) as DaySansMonth
    from PMStationTightenings
    group by datepart(day, SomeTime)

It does what's supposed but not conveniently presented. So I remodel it to include the month as well.

select 
  count(datepart(day, SomeTime)) as NumberOf, 
  datepart(month, SomeTime) + ' ' + datepart(day, SomeTime) as DayAndMonth
    from PMStationTightenings
    group by datepart(month, SomeTime) + ' ' + datepart(day, SomeTime)

The computer tries to be smart and adds the numbers anyway, so instead of e.g. 8 5 I get 13. Less than perfect. Why? (I understand it interprets the two numbers as integers, of course, but why? There's clearly a space between...)

Anyhow, I go ahead and make it trip but putting in stuff it can't add. Now I think to myself "HA! got ya!". Would you believe - the stupid computer barks at me throwing these nasty red stuff like if I was to have made a mistake. Unbelievable! What a nerve! :)

select 
  count(datepart(day, SomeTime)) as NumberOf, 
  datepart(month, SomeTime) + '|' + datepart(day, SomeTime) as DayAndMonth
    from PMStationTightenings
    group by datepart(month, SomeTime) + '|' + datepart(day, SomeTime)

How can I made the computer by b!"#¤ and force it to give me a month/day combo from the DB?

Answer 1

As Jim already explained how you can fix this, here is the WHY.

Quoting from SQL Server's Data Type Precedence:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned.

You are combining INT (return value from DATEPART) AND (N)VARCHAR, but INT has higher precedence than (N)VARCHAR), so SQL Server tries to treat the whole thing as INT.

In general you should try to avoid implicit conversions and be as explicit as you can if you are dealing with different data types in one expression. So just cast/convert your values into the type that is appropriate.

EDIT: corrected part about the data types involved, due to Anthony Grist

Answer 2

Try casting the value to a varchar:

CAST(datepart(month, SomeTime) AS VARCHAR(2)) 
+ ' ' 
+ CAST(datepart(day, SomeTime) AS VARCHAR(2))