Hi Guys,

Is there a function that will convert the date field to a number with the query?

I know the date.num() but when I'm inserting it like this I got an error.


select whinr140.*
from whinr140
where whinr140.item = {:whwmd215.item} and
whinr140.cwar = {:whwmd215.cwar} and
date.num(whinr140.idat) < 1
order by whinr140.idat
selectdo
code here .......
endselect


help please,
amator

select whinr140.*
from whinr140
where whinr140.item = {:whwmd215.item} and
whinr140.cwar = {:whwmd215.cwar} and
date.num(whinr140.idat) < 1
order by whinr140.idat
selectdo
code here .......
endselect

Try select whinr140.*
from whinr140
where whinr140.item = {:whwmd215.item} and
whinr140.cwar = {:whwmd215.cwar} and
whinr140.idat < 1
order by whinr140.idat
selectdo
code here .......
endselect

Hi OmeLuuk,

You are right and I'm sorry for the weak quest. What I really want to do is like this.


select whinr140.*
from whinr140
where whinr140.item = {:whwmd215.item} and
whinr140.cwar = {:whwmd215.cwar} and
date.num(whinr140.idat) - utc.date() < 1
order by whinr140.idat
selectdo
code here .......
endselect


utc.date() is the current date.

can you help me on this?

Thank you,
Amator

Hi,

Don't complicate the select loop. Also, I think, you are trying to fetch the records which are less than a day old right?

For that, you can simply do this -

domain tfgld.date curr.date
long mm,dd,yy,hh,mn,ss

curr.date = date.num()

select whinr140.*
from whinr140
where whinr140.item = {:whwmd215.item} and
whinr140.cwar = {:whwmd215.cwar}
order by whinr140.idat
selectdo
|# Convert the UTC to Date and check if difference is 0 i.e. less that 1
utc.to.date(whinh140.idat, yy, mm, dd, hh, mn, ss)
if curr.date - date.to.num(yy,mm,dd) = 0 then
|* your logic here
endif
endselect

Then what about

select whinr140.*
from whinr140
where whinr140.item = {:whwmd215.item} and
whinr140.cwar = {:whwmd215.cwar} and
whinr140.idat - :1 < 1
order by whinr140.idat
wherebind(1, utc.num())
selectdo
code here .......
endselect

Provided that whinr140.idat represents an UTC date.
At runtime the wherebind will incorporate the (external for SQL) function value in the SQL statement.

@bhushanchanda: better simple than complex

OmeLuuk,

That will work, but I am guessing, the requirement is to select the records from table whose inventory date falls today. But, lets see.

Hi,

This is the scenario, I want to get the total of the Inventory on hand of the Item with the Days before Expiry is <= 0 ("Inventory Date - Current Date <=0" Days before expiry)

So at first I'm trying to get the sum without the Days before expiry.

This is what my query looks like:


select sum(whinr140.qhnd)
from whinr140
where whinr140.item = {:item} and
whinr140.cwar = {:whse}
selectdo
alex = "will get the sum of Qty on Hand of the Item"
endselect


In that there is no issue, then when I'm trying to break the Qty by its Age/Days before expiry I tried this:


select sum(whinr140.qhnd)
from whinr140
where whinr140.item = {:item} and
whinr140.cwar = {:whse} and
whinr140.idat - utc.num() <= 0
selectdo
alex = "will get the sum of Qty on Hand of the Item with the Age of <=0"
endselect


This is wrong because I'm complicating the Select query so what I did is like this.

I select all the items then I display it in my details section of report.
And this is where I manipulate the records. Every line I add the Qty if the item and whse of the next line is equal I put it in my hold variable until it gets the total. And now Im getting the correct total and displays it in my after.field section.

Thank you,
Amator