how to get current month data

[MD1]

Hi Expert !

I want to know about ,how to get current month goods issue data.

SELECT t0."DocNum" AS "DocNum", T0."DocDate" AS "Date", T1."ItemCode" AS "ItemCode",
T1."Dscription" AS "ItemDesc", t3."ItmsGrpNam", T1."Quantity" AS "Received Qty", T0."Comments"
FROM OIGE T0
INNER JOIN IGE1 T1 ON T1."DocEntry" = T0."DocEntry"
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
INNER JOIN OITB t3 ON t2."ItmsGrpCod" = t3."ItmsGrpCod"
WHERE (MONTH(t0."DocDate") = '12' AND MONTH(NOW()) = '1' AND YEAR(t0."DocDate") = (YEAR(NOW()) -1)

Thanks

Danish

[kothandaraman_nagarajan]

Hi,

Try this,

SELECT t0."DocNum" AS "DocNum", T0."DocDate" AS "Date", T1."ItemCode" AS "ItemCode", T1."Dscription" AS "ItemDesc", t3."ItmsGrpNam", T1."Quantity" AS "Received Qty", T0."Comments"

FROM OIGE T0 INNER JOIN IGE1 T1 ON T1."DocEntry" = T0."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" INNER JOIN OITB t3 ON t2."ItmsGrpCod" = t3."ItmsGrpCod"

WHERE

DAYOFMONTH(t0."DocDate") = MONTH(CURRENT_DATE)

AND YEAR(TO_DATE(t0."DocDate")) = YEAR(CURRENT_DATE)

Regards,

Nagarajan

[i_mutinelli]

Dear Danish,

Use GETDATE() instead of NOW() function.

WHERE (MONTH(t0."DocDate") = MONTH(GETDATE())... etc

Regards, Ivan

[Johan_H]

Hi Ivan,

Personally I am actually not very familiar with HANA syntax. Does GETDATE() also work in HANA? I just figured that NOW() was the HANA equivalent of GETDATE().

Regards,

Johan

[MD1]

2018-05-29-135132.png

Error during compiling regular expression

[i_mutinelli]

Hi Johan,

yes sorry, GETDATE is for SQL.

In HANA you can use NOW() or CURRENT_DATE

I try this one, with less, and work. It's work also for > and <>

WHERE MONTH(NOW()) < (MONTH(T0."DocDate"))

It's strange, may be a bug?

Ivan

[Johan_H]

Hi Ivan,

Can you make it work with equal sign = ? Or maybe HANA requires the == syntax?

I don't think it is a bug. Danish is no longer answering so I think there was just a syntax error in his code, and he got it to work now.

Regards,

Johan

[i_mutinelli]

Hi Johan,

I found this SAP Note: https://launchpad.support.sap.com/#/notes/2406955

It's exactly the same situation. Unfortunately it's a HANA bug.

Regards,

Ivan

[Johan_H]

Hi Danish,

MONTH(t0."DocDate") = MONTH(NOW()) AND YEAR(t0."DocDate") = YEAR(NOW())

Regards,

Johan

[MD1]

Could not execute 'SELECT t0."DocNum" AS "DocNum", T0."DocDate" AS "Date", T1."ItemCode" AS "ItemCode", ...' in 12 ms 31 µs .
SAP DBTech JDBC: [2048]: column store error: search table error: [6861] Error during compiling regular expression

[MD1]

please help on above error

[Johan_H]

SELECT t0."DocNum" AS "DocNum", T0."DocDate" AS "Date", T1."ItemCode" AS "ItemCode", 
T1."Dscription" AS "ItemDesc", t3."ItmsGrpNam", T1."Quantity" AS "Received Qty", T0."Comments" 
FROM OIGE T0 
INNER JOIN IGE1 T1 ON T1."DocEntry" = T0."DocEntry" 
INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode" 
INNER JOIN OITB t3 ON t2."ItmsGrpCod" = t3."ItmsGrpCod" 
WHERE MONTH(t0."DocDate") = MONTH(NOW())

  AND YEAR(t0."DocDate") = YEAR(NOW())

[MD1]

Sir Same error

[Johan_H]

Hi Danish,

Did the query that you posted work?

Regards,

Johan

[MD1]

yes sir i posted.2018-05-29-121541.png

[Johan_H]

Sorry Danish, I meant the query in your original question. Did that query work?