cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member209066
Active Contributor
0 Kudos

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

Jos_Dielemans
Active Participant
0 Kudos

Thanks Nithi, this was the correct answer to my question.

former_member209066
Active Contributor
0 Kudos

Hi,

Thank you.

Please close your thread.

Thanks,

Nithi

Answers (3)

Answers (3)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

If you got answer, please close this thread here with correct/helpful answer.

Thanks & Regards,

Nagarajan

KennedyT21
Active Contributor
0 Kudos

Hi Jos...

Try This

SELECT t0.[Docnum],

   t0.[docdate],

   (Cast((Datepart(YEAR,T0.[DocDate])) AS Varchar) +'-'+ RIGHT('00' + CAST(DATEPART(MONTH, T0.DocDate) AS varchar(2)), 2)) AS Period,

   t0.[doctotal]

FROM OINV T0

WHERE t0.[docdate] >= '20140101'



Or


SELECT t0.[Docnum],

   t0.[docdate],

   (Cast((Datepart(YEAR,T0.[DocDate])) AS Varchar) +'-'+ RIGHT('00' + CAST(DATEPART(MONTH, T0.DocDate) AS varchar(2)), 2)) AS Period,

   t0.[doctotal]

FROM OINV T0

WHERE t0.[docdate] >= '[%0]'



Hope Helpful


Rgds

Kennedy


kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT t0.[Docnum], t0.[docdate],

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

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

, t0.[doctotal] FROM OINV T0

where t0.[docdate] >= '20140101'

Thanks & Regards,

Nagarajan