CODEWITHSUNDEEP
sql-servert-sqldate
user11681655
user11681655

Reputation: 23

How to convert a date column in TSQL to display only the month and year

I have a column called DateId that is an int, and displays the date as 20190626. I was able to convert it to a date using the below query, but I would like to display it only as Month and Year.

I have three columns DateId, Customer and PayAmount, all from the same table. There are Many customers who have multiple payments each month and I would like to group by each month combining all the payments each month per customer.

Select 
    Convert(Date, Convert(Char(8), DateId), 112) As [Date], 
    Sum(PayAmount), Customer 
from 
    Pay 
Group By 
    Customer, DateId

What I get:

DateId         PayAmount     Customer
--------------------------------------
2019-06-20       $100           A
2019-06-24       $200           B
2019-04-22       $100           B
2019-03-20       $300           A
2019-04-22       $100           B
2019-06-21       $200           A
2019-06-21       $100           B

What I want: 

DateId         PayAmount     Customer
-------------------------------------
2019-06         $300           A
2019-06         $300           B
2019-04         $200           B
2019-03         $300           A

Upvotes: 1

Views: 93

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 82010

Another option is to use the implicit conversion. In this case, LEFT(DateID,7).

Example Updated for INT

Select DateID      = stuff(left(DateID,6),5,0,'-')
      ,[PayAmount] = sum([PayAmount])
      ,Customer
 From  YourTable
 Group By left(DateID,6),Customer

Returns

DateID   PayAmount      Customer
2019-03  300            A
2019-06  300            A
2019-04  200            B
2019-06  300            B

Upvotes: 1

Thom A
Thom A

Reputation: 96016

This seems like you just need the first 7 characters of the ISO8601 style:

CONVERT(varchar(7),DateId,126)

If you want to retain the date datatype, then you'll need to "round" the date to the start of the month. One common way of doing this is the below method of getting the difference in months between the value and date "0" (1900-01-01):

CONVERT(date,DATEADD(MONTH,DATEDIFF(MONTH, 0, DateId),0))

If you then need to show the format yyyy-MM you can set that as your format in your presentation layer.

If you need to group by that as well, then add that expression to your GROUP BY clause.

Upvotes: 0

Related Questions