Hi ,
I want to filter data by current date and date field in the database.

How to do it ?

I find an example script in baanboard, ex. BOM
But , i found it will not work.

domain tcdate eff.date
eff.date = DATE.NUM()

select tdpur045.item,tdpur045.quap
from tdpur045
where tdpur045.date >= {:eff.date} and
tdpur045.date <= {:eff.date}

In Debug mode ,i find eff.date is very smaller , and tupur045.date is very big.

Thank you.

Hi,

Date.Num returns the number of days from 01-01-0001 to the system date.

Check the following links:
http://www.baanboard.com/baanboard/showthread.php?t=16190

Dates, times, time zones synopsis:
http://www.baanboard.com/programmers_manual_baanerp_help_functions_date_time_zones_synopsis

I hope it helps,

- George

What is wrong, please see below scripts.
extern domain tiutcd eff.date | effectivity date

select tdipu010.item
from tdipu010,tcibd001
where tdipu010.item = :item.pur | Item Range
and tdipu010.item = tcibd001.item | Item Code
and tdipu010.citg = tcibd001.citg | Item Group
and tdipu010.efdt <= :eff.date | Effective Date
and tdipu010.exdt >= :eff.date | Expiration Date
as set with 1 rows
selectdo
| message("Pur Item:%s",item.pur)
selectempty
endselect

Why is the tcibd001 table even in the query? You do not pull any fields nor is any of the where clause checking an external variable. Is this the actual query? I am just wondering if the tpipu010.efdt is actual part of the tcibd001 table. Since I do not know baan 5 or these tables this is just a guess.

Hi,

Your query is looking for ipu010 records with item filled and itemgroup filled. This is not ok. Only one of the two is filled.
We use the next query:
select tdipu010.*
from tdipu010
where tdipu010._index2 = {:i.otbp}
and (tdipu010.efdt <= :i.efdt and
tdipu010.exdt >= :i.efdt)
and (tdipu010.citg = :l.citg or tdipu010.citg = "")
and (tdipu010.item = :i.item or tdipu010.item = "")
and (tdipu010.sfbp = :i.sfbp or tdipu010.sfbp = "")
order by tdipu010.citg asc, tdipu010.item desc,
tdipu010.sfbp desc
as set with 1 rows
selectdo
o.mess = ""
return
endselect

Regards, Adriaan

Dear All,
Thank you for your help.

I found the problem is , in baan 5 , it will use UTC , So should use utc.num

Thank you.