Hi,

I have following SQL:

SELECT                              
 Programska_vsebina,               
 Start
FROM                                
 Programska_shema                  
WHERE                                 
 Start BETWEEN '2008-03-01 06:00:00' and '2008-03-31 19:59:59'

Field Start type is timestamp.

I want to see all records in march with (start) time between 6:00 and 19:59.

Result of this query is wrong - there are records with time part of field Start
greater than 19:59:59 (like 2008-03-18 23:42:00) - see picture in binaries
with wrong record #1 and #4

Does BETWEEN work only with dates or with date&times (timestamp)


This query returns wrong records also:

SELECT                              
 Programska_vsebina,               
 Start
FROM                                
 Programska_shema                  
WHERE                                 
 Start >= '2008-03-01 06:00:00' and Start <= '2008-03-31 19:59:59'

What am I doing wrong?

Regards, Joze

Joze,

I see nothing wrong with the result you are obtaining.
I suspect that what you want is:

SELECT
  Programska_vsebina,
  Start
FROM
  Programska_shema
WHERE
  Cast(Start as date) BETWEEN '2008-03-01' and '2008-03-31' AND
  Cast(Start as time) BETWEEN '06:00:00' and '19:59:59'



--
Fernando Dias
[Team Elevate]

Jose,

> Does BETWEEN work only with dates or with date&times (timestamp)
It works with all of them.

> What am I doing wrong?
You are specifying an continuous interval starting on Mar,1 at 06:00:00
and ending on Mar,31 at 19:59:59, and it seems that's not what you want.

--
Fernando Dias
[Team Elevate]

Hi Fernando,

thanks for your quick answer.

I realized  my bad logic so while you answered me (thanks for your solution)
I also found a solution:

SELECT                              
 Programska_vsebina,               
 Start
FROM                                
 Programska_shema                  
WHERE                                             
 Start between '2008-03-01'  and  '2008-03-31'
 AND
 Extract(Hour from Start) between 6 and 19

and it works also.

Late hour here in Slovenia and my bad logic was a reason for my question...

Still I learned using cast in your example.

Thanks again.

Best regards,

Joze

On Thu, 03 Apr 2008 22:52:43 +0100, "Fernando Dias [Team Elevate]"
<fernandodias.removthis@easygate.com.pt> wrote:

>Joze,
>
>I see nothing wrong with the result you are obtaining.
>I suspect that what you want is:
>
>SELECT
>   Programska_vsebina,
>   Start
>FROM
>   Programska_shema
>WHERE
>   Cast(Start as date) BETWEEN '2008-03-01' and '2008-03-31' AND
>   Cast(Start as time) BETWEEN '06:00:00' and '19:59:59'

Joze,

<< Still I learned using cast in your example. >>

That's what I really enjoy about following these Elevate newsgroups.
You can work out the solution yourself, and you STILL learn something
from a helpful team member! And everyone else learns a bit too.
Even the lurkers.

Regards,
Dan

Dan Rootham wrote:

> Even the lurkers.

Specially the lurkers .. I have had to ask very few questions!
<lurk, lurk>  

--