Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SQL Query how to combine two fields Year and Month to one field Period

Hello,

I have the following Query:

SELECT t0.[Docnum], t0.[docdate],  (Datepart(Year,T0.[DocDate])) , (Datepart(Month,T0.[DocDate])), t0.[doctotal] FROM OINV T0 where t0.[docdate] >= '20140101'

This results in a list of AR/Invoices with Docnum, Docdate, Year, Month, Doctotal in a separate columns.

I would like to combine the two columns "Year" and "Month" in one column "Period" which is written as follows:

2014-01

2014-02

.....

2014-09

2014-10

2014-11

2014-12

2015-01

The zero at the months lower than 10 is essential because of sorting issues.

what statement do i have to use to realise this ?

Who can help me with this ? - Thanks in advance

Tags:
Former Member
Former Member replied

Hi,

Please check this

SELECT t0.[Docnum], t0.[docdate],  Cast((Datepart(Year,T0.[DocDate])) as Varchar) ,

RIGHT('00' + CAST(DATEPART(MONTH, T0.DocDate) AS varchar(2)), 2),

(Cast((Datepart(Year,T0.[DocDate])) as Varchar) +'-'+

RIGHT('00' + CAST(DATEPART(MONTH, T0.DocDate) AS varchar(2)), 2)) as YearandMonth

, t0.[doctotal] FROM OINV T0

where t0.[docdate] >= '20140101'

Thanks,

Nithi

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question