1

I have a table where the values in the date column are represented like this

2018-01-22 11:26:28.000

I run the following query in order to group the sums given the month/ year:

select 
    sum(number) as 'Number', 
    month(Datetime) as 'Month', 
    year(datetime) as 'Year' 
from 
    tablename
group by 
    month(datetime), year(datetime) 
order by 
    year(datetime), month

Which returns an output like this:

Number | Month  | Year
1000   |   01   |  2018
2000   |   02   |  2018

What I now need to need is to concatenate the month and year column into one without changing the table/date column itself. I am aware of this query:

SELECT CAST(CAST([year]  AS VARCHAR(4)) + '-' + 
        CAST([month] AS VARCHAR(2)) + '-' + 
        CAST([day]   AS VARCHAR(2)) 
   AS DATE) AS DateTim FROM table

However, I cant get this to work given the fact that the table is not structured having month/year as a column to begin with. I also cannot change the table or create a new one.

The desired end result would be a query that returns results similar to this:

Number | NewDate
1000   |  01-2018
2000   |  02-2018

Any help would be greatly appreciated. Thank you in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nunga
  • 31
  • 5
  • 3
    This is formatting the display value of a datetime value. It should be done on the client side, not directly from the database. – Sean Lange Jan 22 '18 at 21:34
  • So you already have the year and month taken from the column. Just put those into the other query you have and you have it how you want it. – Sami Kuhmonen Jan 22 '18 at 21:36
  • Have you tried this: 'SELECT ..., CAST( month(Datetime) as VARCHAR( 2)) + '-' + CAST( year(datetime) as VARCHAR( 4 ))' ? – Alex Jan 22 '18 at 23:06

0 Answers0