"date-time" problem

[Former Member]

Hi gurus .)

I have to do report dependent on date and time - that means for example:

since 15.2.2010 6:00 to 16.2.2010 7:00.

I have SELECT command with WHERE clause and it looks like this:

WHERE

( tab2_{erdat => date_from AND tab2}cputm => time_from )

AND ( tab2_{erdat <= date_to AND tab2}cputm <= time_to )

but it gives me results from 15.2.2010 6:00 till 7:00 and from 16.2.2010 6:00 till 7:00.

So this result give me two hours, but I need interval of 25 hours.

What's wrong? ..

How can I get right result?

[Former Member · Accepted solution]

Hi,

easy to answer (and forget adddate and the like in this context).

you do have 4 variables (shortened way of writing)

date_from 15.2.

date_to 16.2.

time_from 6

time_to 7

and if you ask for

date >= 15.2. AND time >= 6

and

date <= 16.2. AND time <= 7

you say 15.2. <= date <= 16.2.

AND 6 <= time <= 7

as predicates combined by AND can be sorted in any order.

So, noone really familiar with predicates is astonished.

you mean:

( date = date_from and time >= time_from

OR

date > date_from )

A N D

( date = date_to and time <= time_to

OR

date < date_to )

do NOT miss the brackets.

let's check:14.2. 6 o'clock : neither = date_from nor >= date_from -> the second AND-part is of no intereset

15.2. 2 o'clock : = date_from, but time < time_from-> first or-part of first and-part is false, second or-part

is false, -->second and-part is of no interest

15.2. 6:30 = date_from, time >= time_from AND date < date_from -> found result

15.2. 8 o'clock = date_from, time >= time_from AND date < date_from -> found result and one of those

you did not find with your predicates

further checks are your turn.

And these predicates will do even if date_from and date_to do not differ just by one day or if time_to < time_from.

Good luck,

Elke

[Former Member · Accepted solution]

Thanks Elke,

it works great. .)

[former_member524429]

Hi,

I think this is not a correct forum to ask such questions.

Anyway, you can use the combination of various SQL functions such as dateadd(), getdate() or datediff() to get your expected result. Please do trial and error for displaying data . You will get it.

Regards

Bhavik G. Shroff