Dear all,
In a form, there is

from date.f time.f to date.t time.t

The error script is:

where date between date.f and date.t
and time between time.f and time.t

Is there any function can integrate date and time to compare? :confused:

Hej,

what kind of error message do you get?

It might be that you only forgot the ":".

where date between :date.f and :date.t
and time between :time.f and :time.t


:rolleyes:

sorry I missed the ":", but this script is wrong, we should not compare date and time respectively

Change the form fields date.f/t and time.f/t to one date.f/t by using a utc format, which includes also the time. Then in the program script you can easily call "where ttmmm999.date inrange :date.f and :date.t"

on form you make two input fields:
- date.f (domain tcdate or similar utc-domain)
- date.t ( " ")
You will see that there is a time field on it as well, because utc format is a date calculated in number of seconds...


Good luck!

En

Hi En,
Do you mean that I should change the field domain to utc-domain? I am not very clear this :(

Cu

do not change the domain, just assign a different domain to the field. For instance check domain tcdate, the data type is normally utc-date. So in the declaration of the form field, you should assign the utc-domain to the field, and also in the program scrip field-declarations.

UTC Formats are not default in Baan IV. Most date fields are non-utc. In Baan IV the tcdate domain is not UTC.

You can combine the date and time fields in SQL to get a good combined field range. It's the one good use for the 'between' option! I use this trick a lot in Baan IV:


|* stdt = Start Date
|* sttm = Start Time

select tihra900.*
from tihra900
where tihra900._index1 inrange {:emno.f} and {:emno.t}
and {tihra900.stdt, tihra900.sttm} between {:stdt.f, :sttm.f}
and {:stdt.t, :sttm.t}

Thank you both!!! :)

Of course Lisa, to program it is not a problem, but I meant more the general way of working. Why using two fields date/time in they are actually just one field, and that one field can perfectly be handled by using utc-format...

En - you are 100% correct in the Baan V environment. Dates on the tables are stored in UTC format.

However in Baan IV they are not. They are 2 separate fields on the table, with non-utc domains. If you create a utc domain field on the form and try to read the tables with it, it will not find a match.

UTC format is one of the big tools improvements they made from IV - V. The UTC domain availablility in Baan IV was ported to IV for the conversion programs from IV - V. The tables in Baan IV were not changed to use it.

Baan IV uses the date.num() and related expressions, or what get in Baan V when you use the 'utc.to.local' command for date and time separate.

When you are checking date and time seperately, you need to provide more conditions. If the date is between the from and to date, there may not be a need to check the time because you are between the two dates. If you date is equal to the from date, the time should be greater than the from time. The way that your code example reads implies that I want information for every day from the from date to the to date, but only betwee the hours of say 1:00 and 3:00. You may really be asking for all information between 1:00 on the from date to 3:00 on the to date.

I hope this was clear enough.

Lisa, you're right. Sorry, am lately so involved with Baan V only, that I didn't even think of non-utc table-date-fields when using date/time...

Also, Toolswizard - the 'inrange' and 'between' are to solve exactly what you described. The 'inrange' would check each field date and time separately, and need a lot more checks as you mention. The 'between' option is what combines them, and allows for a true range for Date and Time together.

I'm sorry but I disagree. The inrange will seperate each field into its own condition. You need to look at the time on the start date and end date differently than you would for the dates inbetween. However, I do like your example of combining the fields first, then using a between. I am going to give that a try and see if it gives me the same results.

We do not differ - maybe I did not explain right.

inrange = each field separate
between = combined fields as a range

Between is only relavant with combined fields, otherwise it acts the same as inrange.

I'm having problems with dates. I'm using the code below, but it doesn't select all the data with the date condition. I tried using inrange and between, but it does the same thing. Can anyone help?


yr04.f = date.to.utc(2004, 01, 01, 0, 0, 0)
yr04.t = date.to.utc(2004, 12, 31, 23, 59, 59)

select sum(whinr110.qstk):total.production,
whinr110.koor,
whinr110.kost,
whinr110.item,
whinr110.trdt
from whinr110
where whinr110.item = :print.item
and whinr110.koor = tckoor.act.sfc |Production
and whinr110.kost = tckost.prd.mat.issue
and whinr110.trdt >= :yr04.f
and whinr110.trdt <= :yr04.t
group by whinr110.item,whinr110.koor,whinr110.kost,whinr110.trdt
selectdo
endselect

I'm having problems with dates. I'm using the code below, but it doesn't select all the data with the date condition. I tried using inrange and between, but it does the same thing. Can anyone help?


yr04.f = date.to.utc(2004, 01, 01, 0, 0, 0)
yr04.t = date.to.utc(2004, 12, 31, 23, 59, 59)

select sum(whinr110.qstk):total.production,
whinr110.koor,
whinr110.kost,
whinr110.item,
whinr110.trdt
from whinr110
where whinr110.item = :print.item
and whinr110.koor = tckoor.act.sfc |Production
and whinr110.kost = tckost.prd.mat.issue
and whinr110.trdt >= :yr04.f
and whinr110.trdt <= :yr04.t
group by whinr110.item,whinr110.koor,whinr110.kost,whinr110.trdt
selectdo
endselect

Hej,

give it a try like this, just to see if you get all the records you need than:


yr04.f = date.to.utc(2004, 01, 01, 0, 0, 0)
yr04.t = date.to.utc(2004, 12, 31, 23, 59, 59)

select whinr110.koor, whinr110.kost, whinr110.item, whinr110.trdt
from whinr110
where whinr110.item = :print.item
and whinr110.koor = tckoor.act.sfc
and whinr110.kost = tckost.prd.mat.issue
and whinr110.trdt between :yr04.f and :yr04.t
selectdo
endselect

Hello,

Since you said in your note that you have tried this with between as well, I think Marcel's solution won't deliver much result.

Try to give us some more information. The variables yr04.f and yr04.t, are they of type long? [They should be]
When you say not all the data is selected, can you give me a few examples of records that should have been selected but do not; and what is their transaction date? Do those records answer all the other conditions?

Regards,

En.

Hej,

I changed two importand lines in the select statement that I think could cause that behaviour.

First:
select sum(whinr110.qstk):total.production,

Second:
group by whinr110.item,whinr110.koor,whinr110.kost,whinr110.trdt

Please give it a try anyway. It can't hurt.

The variables yr04.f and yr04.t are of type tcdate, I tried setting them to long, but no luck. When I say not all the data is selected, I mean that without the date condition I get all the records with the other conditions. Within those conditions, all the dates are within the range that I look for when I add the date condition, i.e. they are all in 2003, but when I put the date condition in, it doesn't retrieve all the data that is within that year range.

I just noticed that if I don't use the date range condition, but just select the date, I don't get all my data. This seems strange.

Did you still have the group by condition in there and only select the transaction date? I don't think that will work, actually I don't think it compiles, so maybe I am mis understanding.

Anyway, it does not look bad to me. I agree, give us the full key for a record you are expecting to read. What is the:

item
koor
kost
trdt

Also, when you debug, what is the value you hold in yr04.f and yr04.t, we can translate those and make sure the proper range is taken - the statements look fine.

Also, just to clarify - malutz's similified code did not work either? Lets get that going then get the group by / sum in there when we are getting the records.

I got it working. I took out the select of trdt.


Original
yr04.f = date.to.utc(2004, 01, 01, 0, 0, 0)
yr04.t = date.to.utc(2004, 12, 31, 23, 59, 59)

select sum(whinr110.qstk):total.production,
whinr110.koor,
whinr110.kost,
whinr110.item,
whinr110.trdt
from whinr110
where whinr110.item = :print.item
and whinr110.koor = tckoor.act.sfc |Production
and whinr110.kost = tckost.prd.mat.issue
and whinr110.trdt >= :yr04.f
and whinr110.trdt <= :yr04.t
group by whinr110.item,whinr110.koor,whinr110.kost,whinr110.trdt
selectdo
endselect



Working
yr04.f = date.to.utc(2004, 01, 01, 0, 0, 0)
yr04.t = date.to.utc(2004, 12, 31, 23, 59, 59)

select sum(whinr110.qstk):total.production,
whinr110.koor,
whinr110.kost,
whinr110.item

from whinr110
where whinr110.item = :print.item
and whinr110.koor = tckoor.act.sfc |Production
and whinr110.kost = tckost.prd.mat.issue
and whinr110.trdt >= :yr04.f
and whinr110.trdt <= :yr04.t
group by whinr110.item,whinr110.koor,whinr110.kost
selectdo
endselect

oooo... not sure why you were getting no results, but I think the transaction date in the group by was much of the problem, it's a tragic flaw of the utc dates. If you try and group by date, well, since the date stores the time as well to the second, you will not get much grouping.

Thanks a lot!

I think you did get all the records before revising your code also, just some records with exact same utc, were grouped together...

I always think of inrange as INDIVIDUAL and between as BUNCHED TOGETHER

One must be very careful with date time mixing, particularly when it crosses midnight

Leslie