need date in the format yyyy-mm in mssql

Question

need the date in the format yyyy-mm and should be grouped to get the count but when i give the

group by date is says invalid

i have found this solution

can anyone help me by providing other solution

select 
count(*) as count, 
Convert(char(10), RH.updated_datetime, 105) as date,
SUM( 
  datediff(SECOND, 
       PRI.procedure_performed_datetime ,RH.updated_datetime
  )
)/count(*) as average_reporting_tat 
from 
report R, 
report_history RH, 
study S, 
procedure_runtime_information PRI, 
priorities PP, 
patient P, 
"procedure" PR 

where 

RH.report_fk=R.pk and RH.pk IN ( 
  select pk from ( 
    select * from report_history where report_fk=r.pk  
  ) as result 
  where old_status_fk IN (21, 27)
) AND R.study_fk = S.pk 
AND S.procedure_runtime_fk = PRI.pk 
AND PRI.procedure_fk = PR.pk 
AND S.priority_fk = PP.pk 
AND PRI.patient_fk = P.pk 
AND RH.updated_datetime >= '2012-05-01' 
AND RH.updated_datetime <= '2013-09-12' 
group by Convert(char(10), RH.updated_datetime, 105)

You can do this SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM’) — blganesh101, Jun 26 '13 at 06:31
you can try this as well ....Left(CONVERT(char(10), RH.updated_datetime, 126), 7) — Anna.P, Jun 26 '13 at 06:38

score 11 · Accepted Answer · answered Jun 26 '13 at 07:37

I think the easiest way to do this is as follows:

CONVERT(VARCHAR(7), RH.updated_datetime, 126)

However, I have never been a fan of converting dates to strings before the application layer, so if it were me I would keep it as a date format, but convert each date the first of the month using:

DATEADD(MONTH, DATEDIFF(MONTH, 0, RH.updated_datetime), 0)

This means your application receives the column as a date, and can be manipulated as a date, sorted as a date etc, then if you did want to display it as yyyy-mm you can do the formatting at the last minute.

score 3 · Answer 2 · answered May 06 '22 at 11:04

3

Update for SQL Server 2012+

I always find the CONVERT magic numbers hard to remember. You can use FORMAT instead and provide a formatting pattern string like this:

SELECT FORMAT(GetDate(),'yyyy-MM')

See Also: Convert date to YYYYMM format

answered May 06 '22 at 11:04

KyleMit

30,350
66
462
664

score 0 · Answer 3 · answered Jun 26 '13 at 07:01

Try this:

select  count(*) as count,Convert(char(10), RH.updated_datetime, 105) as date,
           (CAST(MONTH(RH.updated_datetime) AS VARCHAR(2)) + '-' +              CAST(YEAR(RH.updated_datetime) AS VARCHAR(4))),
        SUM(datediff(SECOND,PRI.procedure_performed_datetime ,RH.updated_datetime))/count(*) as average_reporting_tat 
from 
        report R, 
        report_history RH, 
        study S, 
        procedure_runtime_information PRI, 
        priorities PP, 
        patient P, 
        "procedure" PR 
where 
        RH.report_fk=R.pk and RH.pk IN (select pk from (select * from report_history where report_fk=r.pk ) as result where old_status_fk IN (21, 27)) 
        AND R.study_fk = S.pk 
        AND S.procedure_runtime_fk = PRI.pk 
        AND PRI.procedure_fk = PR.pk 
        AND S.priority_fk = PP.pk 
        AND PRI.patient_fk = P.pk 
        AND RH.updated_datetime >= '2012-05-01' 
        AND RH.updated_datetime <= '2013-09-12'
group by (CAST(MONTH(RH.updated_datetime) AS VARCHAR(2)) + '-' + CAST(YEAR(RH.updated_datetime) AS VARCHAR(4))),date

score 0 · Answer 4 · answered Jun 26 '13 at 07:04

Try this one -

SELECT 
       [count] = COUNT(1) 
     , [date] = CAST(YEAR(RH.updated_datetime) AS VARCHAR(4)) + '-' + CAST(MONTH(RH.updated_datetime) AS VARCHAR(2))
     , average_reporting_tat = SUM(DATEDIFF(SECOND, PRI.procedure_performed_datetime ,RH.updated_datetime))/COUNT(1)  
FROM dbo.report R
JOIN dbo.report_history RH ON RH.report_fk=R.pk 
JOIN dbo.study S ON R.study_fk = S.pk 
JOIN dbo.procedure_runtime_information PRI ON S.procedure_runtime_fk = PRI.pk  
JOIN dbo.priorities PP ON S.priority_fk = PP.pk  
JOIN dbo.patient P ON PRI.patient_fk = P.pk  
JOIN dbo.[procedure] PR ON PRI.procedure_fk = PR.pk 
WHERE RH.pk IN ( 
         SELECT pk 
         FROM report_history 
         WHERE report_fk=r.pk AND old_status_fk IN (21, 27)
     )  
     AND RH.updated_datetime BETWEEN '2012-05-01' AND '2013-09-12' 
GROUP BY CAST(YEAR(RH.updated_datetime) AS VARCHAR(4)) + '-' + CAST(MONTH(RH.updated_datetime) AS VARCHAR(2))

need date in the format yyyy-mm in mssql

4 Answers4

Update for SQL Server 2012+

Linked