[Resolved] Group date and time

snufse · #1 (**permalink**) October 23rd, 2008, 03:30 PM

I have a sales ticket table where I need to select records by date and time. Need to be able to group "from date/time" as lower limit and group "to date/time" as upper limit. I tried below but it does not produce results even I have have data in the table.

select * from salestkt where (ticket_date >= '2008-10-20' and ticket_time >= '06:00:00 AM') and
(ticket_date <= '2008-10-22' and ticket_time <= '06:00:00 AM')

Old Pedant · #2 (**permalink**) October 23rd, 2008, 04:42 PM

Well, your very big goof was putting ticket_date and ticket_time into separate fields.

As you have it now, you have an IMPOSSIBLE TO MEET set of conditions.

Please understand that when you have a WHERE clause where all the conjunctions are AND, then your parentheses are not doing anything, at all.

So your query

Code:

select * from salestkt 
where (ticket_date >= '2008-10-20' and ticket_time >= '06:00:00 AM') 
and (ticket_date <= '2008-10-22' and ticket_time <= '06:00:00 AM')

Is 100% the same as

Code:

select * from salestkt 
where ticket_date >= '2008-10-20' and ticket_time >= '06:00:00 AM'
and ticket_date <= '2008-10-22' and ticket_time <= '06:00:00 AM'

which is 100% the same as

Code:

select * from salestkt 
where ticket_date >= '2008-10-20' and ticket_date <= '2008-10-22' 
and ticket_time >= '06:00:00 AM' and ticket_time <= '06:00:00 AM'

So now just look at those last two conditions:

Code:

and ticket_time >= '06:00:00 AM' and ticket_time <= '06:00:00 AM'

And of course there is NO POSSIBLE VALUE for ticket_time that can satisfy BOTH of those conditions, so of course you get zero results.

KABLOOEY.

You really really really should combine ticket_date and ticket_time into a single DB field.

But I *THINK* you can do the following with SQL SERVER:

Code:

select * from salestkt 
WHERE (ticket_date + ticket_time ) BETWEEN '2008-10-20 06:00:00 AM' AND '2008-10-22 06:00:00 AM'

That is, I *think* that in SQL Server you can simply add a date and time together to produce a datetime value.

If not, then we can do it with DATEADD and DATEDIFF function calls. But try that first.

Old Pedant · #3 (**permalink**) October 23rd, 2008, 04:47 PM

Just in case, here is the code using DATEADD and DATEDIFF that *should* work if just adding as I showed doesn't:

Code:

select * from salestkt 
WHERE DATEADD( s, DATEDIFF( s, '0:00:00', ticket_time ), ticket_date ) 
      BETWEEN '2008-10-20 06:00:00 AM' AND '2008-10-22 06:00:00 AM'

Old Pedant · #4 (**permalink**) October 23rd, 2008, 05:48 PM

Quote:

quote:
and ticket_time >= '06:00:00 AM' and ticket_time <= '06:00:00 AM'

And of course there is NO POSSIBLE VALUE for ticket_time that can satisfy BOTH of those conditions, so of course you get zero results.

DOH. I fibbed! Actually, there *is* exactly ONE value that meets both conditions:
ticket_time = 06:00:00 AM
exactly. But that's a pretty unlikely time if your dates and times are "real time", and anyway no other time qualifies.

But, still, DOH on me.

snufse · #5 (**permalink**) October 23rd, 2008, 07:44 PM

Hi Old Pedant...

This is a third party data base (actually an Advantage db). Why it was designed this way I don't know. Back to you suggestions:

Code:

select * from salestkt 
WHERE (ticket_date + ticket_time ) BETWEEN '2008-10-20 06:00:00 AM' AND '2008-10-22 06:00:00 AM'

Produces error: poQuery: Error 7200: AQE Error: State = S0000; NativeError = 2124; [Extended Systems][Advantage SQL Engine]Invalid operand for operator: + -- Location of error in the
SQL statement is: 32 (line: 2 column: 8)

Code:

select * from salestkt 
WHERE DATEADD( s, DATEDIFF( s, '0:00:00', ticket_time ), ticket_date ) BETWEEN '2008-10-20 06:00:00 AM' AND '2008-10-22 06:00:00 AM'

Produces error: poQuery: Error 7200: AQE Error: State = S0000; NativeError = 2121; [Extended Systems][Advantage SQL Engine]Column not found: s -- Location of error in the SQL
statement is: 40 (line: 2 column: 16)

The reason we are using "06:00:00 AM" is because this is the shift start/stop and they want all tickets for this period. I tried using "05:59:00 AM" (as end time) but that does not work.

I changed the code to (removed the 'AM' and used military time)

Code:

select * from salestkt where (ticket_date >= '2008-10-20' and ticket_time >= '06:00:00')and                             (ticket_date <= '2008-10-22' and ticket_time <= '23:59:00')

This produces good result. But it still does not like the "06:00:00 AM". I'm confused......

Old Pedant · #6 (**permalink**) October 23rd, 2008, 08:20 PM

So why did you post in a forum that says it is for SQL 2005????

Okay, so we have to find the docs for "Advantage SQL Engine", whatever that is.

Do you have a link to them??

snufse · #7 (**permalink**) October 24th, 2008, 07:36 AM

Here is a link:

http://manuals.sybase.com/onlinebook...ookTextView/65

snufse · #8 (**permalink**) October 24th, 2008, 10:26 AM

Finally got it working:

Code:

SELECT * FROM SALESTKT 
      WHERE (((TICKET_DATE > '2008-10-20') AND (TICKET_DATE < '2008-10-22'))
      OR ((TICKET_DATE = '2008-10-20') AND (TICKET_TIME >= '06:00:00'))
      OR ((TICKET_DATE = '2008-10-22') AND (TICKET_TIME <= '06:00:00')))

Thank you for all your help.

Old Pedant · #9 (**permalink**) October 24th, 2008, 02:35 PM

Wrong document. That's just about how to install and use the product.

THis is the right one:
http://infocenter.sybase.com/help/to...?noframes=true

And you can find DATEADD and DATEDIFF both on page 137, and the syntax is indeed the same as for SQL Server.
Except that you then look on page 95 and find that the correct abbreviation for "seconds" is "ss" and not just "s".

None of this is suprising, since SQL Server is derived from Sybase.

So most likely my answer using DATEADD and DATEDIFF would have worked, just changing the "s" to "ss", thus:

Code:

select * from salestkt 
WHERE DATEADD( ss, DATEDIFF( ss, '0:00:00', ticket_time ), ticket_date ) 
      BETWEEN '2008-10-20 06:00:00 AM' AND '2008-10-22 06:00:00 AM'

*OR*, apparently, you can use the full name, thus:

Code:

select * from salestkt 
WHERE DATEADD( Second, DATEDIFF( Second, '0:00:00', ticket_time ), ticket_date ) 
      BETWEEN '2008-10-20 06:00:00 AM' AND '2008-10-22 06:00:00 AM'

You might want to keep that PDF document handy if you are going to work with this DB any more.

Need to download code?

Navigation