on 02-12-2015 9:48 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
If you got answer, please close this thread here with correct/helpful answer.
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
89 | |
7 | |
7 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.