June 16, 2016 at 10:13 pm
Hello,
I'm not sure how to implement this, I have a Sybase DB 15.4 but I guess if I found the logic in SQL I will figure out it in Sybase,
What I need to get its the following:
I have a table with tickets lets say just 3 columns
Ticket number = int
TicketDate = DATETIME ( But just have the date)
TicketTime = VARCHAR(8) Just have the time in 00:00:00
This is a query so I cannot use parameters,
So when the user run the query if the user it's running the query between 00:00:00
and 06:00:00 then I need to get the results of Yesterday of the tickets with the column TicketDate
Something like Select Ticketnumber, TicketDate, TicketTime
From ticket
WHERE CASE TicketTime WHEN TicketTime BETWEEN
SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME) AND
SELECT CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00600000 AS DATETIME) THEN TicketDate = GETDATE() or current_date() ELSE
TicketDate = (Dateadd(dd, -1, GETDATE())
But the CASE Will not work in the Where Clause, Any Ideas?
Thanks,
Regards
June 16, 2016 at 10:23 pm
I thought in something like this:
select * from tickets
where a.time >= CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME and a.TIME <= CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME
AND a.DATE = Current_Date() OR a.TIME >= CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME and a.TIME <= CAST(SUBSTRING(CAST(GETDATE() AS BINARY(8)),1,4) + 0x00000000 AS DATETIME and a.DATE = Current_Date() -1
Not sure if this could work..
June 22, 2016 at 11:23 am
There are a few contradictions in your question, I think. Do you want the query execution time or the tables ticket time to determine the ticketdate to select?
Either way, try these two:
select *
from tickets
where ticketdate = case when datepart(hour,getdate()) between 0 AND 6 then cast(datediff(d,0,dateadd(d,-1,getdate())) as datetime) else cast(datediff(d,0,getdate()) as datetime) end
or
select *
from tickets
where ticketdate = case when left(ticketTime,2) between '00' AND '06' then cast(datediff(d,0,dateadd(d,-1,getdate())) as datetime) else cast(datediff(d,0,getdate()) as datetime) end
There is a second contradiction between your text and your example query. The text says that you want the previous day if the time is between 00:00:00 and 06:00:00, your query does it the other way around. I have chosen to believe in your text more than in your example query. 😉
June 22, 2016 at 12:21 pm
You don't need CASE statements.
select *
from tickets
where ticketdate = CAST(DATEADD(HOUR, -6, GETDATE()) AS DATE)
If you have a range that you are trying to fit into another range of the same size, you can chop up the first range and rearrange the parts so that they fit, or you can adjust the whole range so that they fit. Generally the second approach is simpler, but most people take the first approach, because they assume that they shouldn't move the piece that already overlaps.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 22, 2016 at 12:40 pm
drew.allen (6/22/2016)
You don't need CASE statements.
select *
from tickets
where ticketdate = CAST(DATEADD(HOUR, -6, GETDATE()) AS DATE)
If you have a range that you are trying to fit into another range of the same size, you can chop up the first range and rearrange the parts so that they fit, or you can adjust the whole range so that they fit. Generally the second approach is simpler, but most people take the first approach, because they assume that they shouldn't move the piece that already overlaps.
Drew
Yes, I see your point. That is definitely more elegant.
I don't know if the OP's Sybase database supports the DATE data type. If it only supports the old DATETIME data type, you'd need to cut off the time part, which you can do with DATEDIFF:
select *
from tickets
where ticketdate = CAST(DATEDIFF(DAY, 0, DATEADD(HOUR, -6, GETDATE())) AS DATETIME)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply