Hi!

I have a table (track.db) with the following fields:
startDate:  Date
startTime: Time

Lets say we have the following records:

Startdate:       StartTime:
2012-8-22     18:00:00
2012-8-23     20:00:00

I want to query all records between the date 22.08.2012 («2012-8-22») from time «18:00:00» to 23.08.2012(«2012-8-23») until time «18:00:00»
(only the first record should be in the result)

Hope someone clever can help.

Kind regards,
Geir Bratlie

Geir,

SELECT
 *
FROM
 Track
WHERE
 ( StartDate > '2012-08-22' OR (StartDate = '2012-08-22' AND StartTime >=
'18:00:00'))  AND
 ( StartDate < '2012-08-23' OR (StartDate = '2012-08-23' AND StartTime <=
'18:00:00'))


--
Fernando Dias
[Team Elevate]

Geir

>I have a table (track.db) with the following fields:
>startDate: Date
>startTime: Time
>
>Lets say we have the following records:
>
>Startdate:      StartTime:
>2012-8-22     18:00:00
>2012-8-23     20:00:00
>
>I want to query all records between the date 22.08.2012 («2012-8-22») from time «18:00:00» to 23.08.2012(«2012-8-23») until time «18:00:00»
>(only the first record should be in the result)
>
>Hope someone clever can help.

You'll have to wait for John Hay for someone clever, but until he shows up try:

SELECT
CAST(CAST(StartDate AS VARCHAR(10))+ ' ' + CAST(StartTime AS VARCHAR(10)) AS TIMESTAMP) AS CompTime,
....
other fields
....
FROM track
WHERE
CAST(CAST(_StartDate AS VARCHAR(10))+ ' ' + CAST(StartTime AS VARCHAR(10)) AS TIMESTAMP)> '2012-08-22 18:00:00'
AND
CAST(CAST(StartDate AS VARCHAR(10))+ ' ' + CAST(StartTime AS VARCHAR(10)) AS TIMESTAMP) < '2012-08-23 18:00:00'
ORDER BY CompTime
TOP 1

Roy Lambert [Team Elevate]

Fernando


Intriguing approach. I always to convert to a timestamp. Re-reading the OP I see I misinterpreted <<(only the first record should be in the result)>> and added a TOP 1 to the sql

Roy Lambert [Team Elevate]

Roy,

<< Intriguing approach.>>

Why, what's intriguing ?

--
Fernando Dias
[Team Elevate]

Fernando


Leaving them as separate entities. I've always found that for me it ends up in getting things wrong.

Roy Lambert

Thank you, guys!

Roy,

It's the 'standard' way of comparing ordered pairs (and how row values are
compared) :

(a1,b1) > (a2,b2)
if and only if
(a1>a2) OR ( (a1=a2) AND (b1>b2) )

It also has the advantage that possible NULL values are not particular
cases, it's always going to give the correct result even when one or more
values are NULL.
I know you don’t like standards, but sometimes they do make sense .

--
Fernando Dias
[Team Elevate]

Fernando


>It's the 'standard' way of comparing ordered pairs (and how row values are
>compared) :
>
>(a1,b1) > (a2,b2)
>if and only if
>(a1>a2) OR ( (a1=a2) AND (b1>b2) )

Forgive me - I've obviously been out of school/university to long and managed to forget. In my defence the number of times this question appears it seems lots of others have as well

>It also has the advantage that possible NULL values are not particular
>cases, it's always going to give the correct result even when one or more
>values are NULL.

Can you produce a truth table for that? Just mentally reviewing it my view is that will give an answer when a1>a2 but will not when a1=a2, and even if it does it shouldn't because with either b1 or b2 as NULL the comparison is invalid. I stopped reviewing at that point.

>I know you don’t like standards, but sometimes they do make sense .

Wrong, I do like standards I just don't accept that once the great and good have issued one it is necessarily right and has to be regarded as a fundamental law of the cosmos!

I see a massive difference between "standard = this is the normal way of doing things" and "standard = this is the way you MUST do it or the great god will sit on you" and "standard = this is what we will measure and calibrate against".

Which one were you quoting?

Roy Lambert [Team Elevate]

Roy,

First of all I have to say that I made a mistake thinking of this matter as if it was EDB SQL, and it's not.
So, when I said 'standard' I was thinking about the SQL 2003 standard. Sorry for my mistake and for not being clear.
That said, I still believe that my mistake doesn't have any significant impact in what I have said in general, except for the fact that there are no Row Values in DBISAM. For the rest, DBISAM evaluates logical expressions following the same rules, including for NULL values.

<< Can you produce a truth table for that? >>

Here it is. Let:
   x = (a1>a2) OR ((a1=a2) AND (b1>b2))
   T = True
   F = False
   N = NULL

(a1>a2) (a1=a2) (b1>b2)  x
-------+-------+-------+---+
   T       F       T     T
   F       T       T     T
   F       F       T     F
   N       N       T     N
   T       F       F     T
   F       T       F     F
   F       F       F     F
   N       N       F     N
   T       F       N     T
   F       T       N     N
   F       F       N     N
   N       N       N     N
------------------------------


<< Just mentally reviewing it my view is that will give an answer when a1>a2 but will not when a1=a2, and even if it does it shouldn't because with either b1 or b2 as NULL the comparison is invalid.>>

What do you mean?
It will always give an answer, there is no such thing as an invalid comparison except if the types are incompatible.
Comparing NULL values is not invalid, it just gives a NULL result, and thats a perfectly valid result.
The fact that you don't like or don't find useful to deal with 3-valued logic doesn't turn it into an alien or invalid thing.
BTW, the expression you made, converting dates and times to varchar and then to timestamp could have surprising results in case of null values, just do a few tests and see by yourself.

<< Wrong, I do like standards I just don't accept that once the great and good have issued one it is necessarily right and has to be regarded as a fundamental law of the cosmos!>>

Amen! That I do agree 100%

--
Fernando Dias
[Team Elevate]