CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3906930
user3906930

Reputation: 77

Query that will convert date into Month with count

I have a table called Worked_Received.

Columns

Received Date  Ticket number
12/08/2014         2135823
13/09/2014         2458956
01/02/2014         2458796
03/07/2014         2456353
04/07/2014         2457896

I would like to have a result like this

Month      Count
August       1
September    1
February     1
July         2

Upvotes: 0

Views: 332

Answers (1)

Arion
Arion

Reputation: 31249

You could do this:

SELECT
    COUNT(*) AS [Count], 
    DATENAME(month,table1.[Received Date]) AS [Month]
FROM
    table1
GROUP BY
    DATENAME(month,table1.[Received Date])

Update

Some of the date can't be converted to a datetime. If you know that the varchar will always have the same format. You can use convert like this:

SELECT
    COUNT(*) AS [Count], 
    DATENAME(month,CONVERT(datetime,table1.[Received Date], 103)) AS [Month]
FROM
    table1
GROUP BY
    DATENAME(month,CONVERT(datetime,table1.[Received Date], 103))

Update 2

The dates that you have in the example data all casts without exception like this:

INSERT INTO @tbl VALUES('12/08/2014'),('13/09/2014'),('01/02/2014'),
('03/07/2014'),
('04/07/2014')

SELECT
 DATENAME(month,CONVERT(datetime, tbl.date, 103))
FROM
@tbl AS tbl

The convertion error that you get is because there is a varchar that can't be converted to a date. To find out which row is not converting you can check this:

SELECT
    *
FROM
    table1
WHERE ISDATE(table1.[Received Date])=0

Reference:

Upvotes: 2

Related Questions