Home » SQL & PL/SQL » SQL & PL/SQL » Select Date/Time past 00:00:00? (Oracle 10g)
Select Date/Time past 00:00:00? [message #351348] Tue, 30 September 2008 07:56 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

I have a table which contains the following two fields: -

TRANS_DATE - 30/09/2008 13:46:34
DATE - 30/09/2008


Now because there is an off-set for the day at e.g. 04:00:00

I could have records such as: -

29/09/2008 22:50:34 --- 29/09/2008
29/09/2008 23:55:06 --- 29/09/2008
30/09/2008 00:14:34 --- 29/09/2008
30/09/2008 01:34:45 --- 29/09/2008


How can I return just the records where the 'DATE' is equal to '29/09/2008' and the 'TRANS_DATE' Time is between '23:00:00' and '00:59:59'

Might be a lame question, but I tried the following and got nothing: -

sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '22:00:00' and '00:59:59' then case when.....
....
where tr.bookkeeping_date BETWEEN to_date('29/01/2008', 'DD-MM-YYYY') AND to_date('29/09/2008', 'DD-MM-YYYY')....


I think I've missed something?

Regards
Re: Select Date/Time past 00:00:00? [message #351351 is a reply to message #351348] Tue, 30 September 2008 08:02 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Add 4/24 to a DATE to get four hours from that date/time.
Re: Select Date/Time past 00:00:00? [message #351353 is a reply to message #351348] Tue, 30 September 2008 08:10 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Thanks for the prompt reply, but how would I use the date add in my Sum statement?

Am I not doing this already, but manually?

Regards
Re: Select Date/Time past 00:00:00? [message #351356 is a reply to message #351353] Tue, 30 September 2008 08:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You only gave partial code so I have no idea what you are trying to do.

Another hint:

September 28th, 11:00pm +4/24 = September 29th, 3:00am

Additionally, using TO_CHAR, nothing can ever be between '2' and '0'.

[Updated on: Tue, 30 September 2008 08:16]

Report message to a moderator

Re: Select Date/Time past 00:00:00? [message #351358 is a reply to message #351348] Tue, 30 September 2008 08:26 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Thanks for the reply.

No doubt giving details of the whole query would help, sorry.

Here is my query: -

select gr.description || ' - ' || pl.description,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '301' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '301' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '302' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '302' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '303' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '303' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '304' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '304' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '15:30:00' and '20:00:00' then case when cr.code in ('301', '302', '303', '304') then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '15:30:00' and '20:00:00' then case when cr.code in ('301', '302', '303', '304') then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '22:00:00' and '03:59:59' then case when cr.code in ('301', '302', '303', '304') then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '22:00:00' and '03:59:59' then case when cr.code in ('301', '302', '303', '304') then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc
            from transactions tr, trans_articles ta, articles ar, groups gr, pricelevels pl, tills cr
            where tr.bookkeeping_date BETWEEN to_date('29/01/2008', 'DD-MM-YYYY')
             AND to_date('29/09/2008', 'DD-MM-YYYY')
            and tr.ID = ta.transaction_id
            and ta.article_id = ar.id
            and ar.group_a_id = gr.id
            and tr.pricelevel_id = pl.id
            and tr.till_id = cr.id
            and gr.code in ('100', '101', '102', '103', '104', '105')
            and ta.delete_operator_id is null
            group by gr.description, gr.code, pl.description, pl.code, ta.vat_percent
            order by pl.code, gr.code


It does use other tables, but the 'TRANS_DATE' and 'BOOKKEEPING_DATE' are from the same table.

I'm trying to show the transactions for a accounting day where the physical transaction might be into the next day, but because there is an off-set @ 04:00:00 transactions made past midnight are still in the same accounting day. Hope that makes sense?

The last two columns are to show the transaction for today plus tomorrow upto 04:00:00.

Regards
Re: Select Date/Time past 00:00:00? [message #351361 is a reply to message #351358] Tue, 30 September 2008 08:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Penfold wrote on Tue, 30 September 2008 09:26
Thanks for the reply.

No doubt giving details of the whole query would help, sorry.

Here is my query: -

select gr.description || ' - ' || pl.description,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '301' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '301' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '302' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '302' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '303' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '303' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '304' then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '04:00:00' and '15:29:59' then case when cr.code = '304' then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '15:30:00' and '20:00:00' then case when cr.code in ('301', '302', '303', '304') then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '15:30:00' and '20:00:00' then case when cr.code in ('301', '302', '303', '304') then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '22:00:00' and '03:59:59' then case when cr.code in ('301', '302', '303', '304') then round((ta.price + nvl(ta.discount, 0)) - ((ta.price + nvl(ta.discount, 0)) / (ta.vat_percent + 100) * ta.vat_percent), 2)else 0 end else 0 end) vat_ex,
            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '22:00:00' and '03:59:59' then case when cr.code in ('301', '302', '303', '304') then ta.price + nvl(ta.discount, 0) else 0 end else 0 end) vat_inc
            from transactions tr, trans_articles ta, articles ar, groups gr, pricelevels pl, tills cr
            where tr.bookkeeping_date BETWEEN to_date('29/01/2008', 'DD-MM-YYYY')
             AND to_date('29/09/2008', 'DD-MM-YYYY')
            and tr.ID = ta.transaction_id
            and ta.article_id = ar.id
            and ar.group_a_id = gr.id
            and tr.pricelevel_id = pl.id
            and tr.till_id = cr.id
            and gr.code in ('100', '101', '102', '103', '104', '105')
            and ta.delete_operator_id is null
            group by gr.description, gr.code, pl.description, pl.code, ta.vat_percent
            order by pl.code, gr.code




Once again, there can never be a character string that falls out between '2' and '0'. Please have a look at your code and don't ignore what I point out.

Second, your format mask does not match your DATEs.

I think your explanation of "offset" is very confusing. I thought you meant you wanted a date range from current through four hours from current, but now I am not so sure. Here's another hint:

trunc of today + 1 + 4/24 is tomorrow at 4:00am.
Re: Select Date/Time past 00:00:00? [message #351366 is a reply to message #351348] Tue, 30 September 2008 09:02 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Hi,

Quote:

Once again, there can never be a character string that falls out between '2' and '0'. Please have a look at your code and don't ignore what I point out.



I'm only converting the time part of the TRANS_DATE field using the TO_CHAR, so unless I'm missing something else the '0' is only being used by the CASE WHEN to return '0' is nothing is retruned from my sum(s)?

Not sure where you get the '2' from as this is being used by the ROUND function?

Quote:

Second, your format mask does not match your DATEs



Are you refering to the bookkeeping_date?

This is only holding a date, but should I still format this to 'dd/mm/yyyy HH24:MI:SS'?

The hint I presume you're suggesting I have this on the 'ADD to_date('29/09/2008', 'DD-MM-YYYY')?

Even thought there is no time stored in this field?

Regards
Re: Select Date/Time past 00:00:00? [message #351406 is a reply to message #351361] Tue, 30 September 2008 12:49 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Penfold wrote on Tue, 30 September 2008 09:26


            sum(case when to_char (tr.trans_date, 'HH24:MI:SS') between '22:00:00' and '03:59:59' then



This is what I was talking about. A character string can never be between '2' and '0'. Do you now see it now?

            where tr.bookkeeping_date BETWEEN to_date('29/01/2008', 'DD-MM-YYYY')
             AND to_date('29/09/2008', 'DD-MM-YYYY')


And that is an invalid format mask for the data.


Previous Topic: Create Table
Next Topic: reset auto generate from 1 when academic year changes
Goto Forum:
  


Current Time: Fri Dec 02 18:41:33 CST 2016

Total time taken to generate the page: 0.30667 seconds