topic Re: Date difference in SQL Where statement in Application Development and Automation Discussions

Date difference in SQL Where statement

Former Member — Wed, 30 May 2007 18:08:18 GMT

I would like to do select in my database table like this:

select * from abc_table

where dtret - dt_current = 6.

where:

dtret is a date field in table,

dt_current has sy-datum.

I can do this? dtret - dt_current works well in all the cases? Or I have to use some function?

Thanks

Re: Date difference in SQL Where statement

Former Member — Wed, 30 May 2007 18:09:56 GMT

Sorry, I forgot to mention that I need to receive the delta in days.

Re: Date difference in SQL Where statement

ferry_lianto — Wed, 30 May 2007 18:19:20 GMT

Hi,

Please try this.


DATA: WA_DATE LIKE SY-DATUM.

WA_DATE = SY-DATUM - 6.

SELECT * 
INTO TABLE ITAB
FROM ABC_TABLE
WHERE DTRET = WA_DATE.

Regards,

Ferry Lianto

Re: Date difference in SQL Where statement

Former Member — Wed, 30 May 2007 18:19:50 GMT

I don't think it is possible.

You can fetch the data first in an internal table.

LOOP through internal table and find out the difference between dtret and dt_current and if it is not equal to 6, delete this record from internal table.

Re: Date difference in SQL Where statement

Former Member — Wed, 30 May 2007 18:19:51 GMT

You can do this way.

Have a variable of type sy-datum and assign value as sy-datum - 6.

Then use this variable in WHERE condition as

WHERE dtret = <your variable>.

Regards,

Raj

Re: Date difference in SQL Where statement

former_member196280 — Thu, 31 May 2007 03:22:55 GMT

Hi Cristiana,

Apply the below code I guess it will solve your problem.

Select * into table itab from abc_table

where dtret NE sy-datum.

LOOP at itab.

IF sy-subrc EQ 0.

dt_current = itab-dtret - dt_current.

IF dt_current EQ 6.

MOVE itab TO itab1.

APPEND itab1.

ENDIF.

ENDLOOP.

Reward points to all useful answers.

Regards,

SaiRam

Re: Date difference in SQL Where statement

Former Member — Thu, 31 May 2007 18:09:37 GMT

My problem is that my abc_table has a looooooot of records....

So I would like to read to my internal table only those ones that match....

Re: Date difference in SQL Where statement

Former Member — Thu, 31 May 2007 18:16:42 GMT

I think Ferry's example is exactly what you are looking for. Have you tried it?

Rob