Home » SQL & PL/SQL » SQL & PL/SQL » Datetime format
Datetime format [message #199583] Wed, 25 October 2006 03:30 Go to next message
deni_sa
Messages: 77
Registered: June 2006
Member
SELECT COUNT (uds_record_id )
FROM udr_may_2006
WHERE xfile_ind ='H'
AND tariff_info_sncode =1
AND((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) BETWEEN TO_DATETIME('5,05,2006 ,23:00:00','dd,mm,yyyy,hh:mm:ss')
AND TO_DATETIME ('6,05,2006,6:00:00', 'dd,mm,yyyy' ,'hh:mm:ss') ;
=-----------------------------------------------------------




I want to execute this query but im facing a problem with the to_datetime format ...

the error i recive is : ORA-00904 To_datetime invalid identifier .

please can you help me
Re: datetime format [message #199586 is a reply to message #199583] Wed, 25 October 2006 03:45 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
What i want to do this query is to show the nr of calls between this hours of a day .
Re: datetime format [message #199587 is a reply to message #199583] Wed, 25 October 2006 03:46 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
to_date will do.
Re: datetime format [message #199588 is a reply to message #199587] Wed, 25 October 2006 03:49 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
NO still error Alien
Re: datetime format [message #199589 is a reply to message #199588] Wed, 25 October 2006 03:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

AND TO_DATETIME ('6,05,2006,6:00:00', 'dd,mm,yyyy' ,'hh:mm:ss') ;


try this way

and to_date('06-05-2006,06:00:00','dd-mon-yyyy hh24:mi:ss');
Re: datetime format [message #199590 is a reply to message #199588] Wed, 25 October 2006 03:53 Go to previous messageGo to next message
Maaher
Messages: 7055
Registered: December 2001
Senior Member
It would be helpful if you told what error. Have you looked up the syntax of TO_DATE?

Check out this:
SQL> select to_date('17/10/2006 23:45:59','dd/mm/yyyy hh24:mi:ss') x
  2  from dual
  3  /

X
---------
17-OCT-06

SQL> alter session set nls_date_format = 'Month DDth, yyyy HH24:mi:ss';

Session altered.

SQL> select to_date('17/10/2006 23:45:59','dd/mm/yyyy hh24:mi:ss') x
  2  from dual
  3  /

X
-----------------------------
October   17TH, 2006 23:45:59


MHE
Re: datetime format [message #199591 is a reply to message #199589] Wed, 25 October 2006 03:53 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
I think the problem is at the between operator
Re: datetime format [message #199592 is a reply to message #199583] Wed, 25 October 2006 03:53 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
Hmmm.... works for me:
SQL> create table udr_may_2006 (record_id number, start_time date, something_else varchar2(20));

Tabel is aangemaakt.

SQL> insert into udr_may_2006 values (1,to_date('1-jan-2006 12:45:34','dd-mon-yyyy hh24:mi:ss'),'Tes
t');

1 rij is aangemaakt.

SQL> select * 
  2  from udr_may_2006 
  3  where start_time between to_date('1,jan,2006,12:00:00','dd,mon,yyyy,hh24:mi:ss') and to_date('1
,jan,2006,13:00:00','dd,mon,yyyy,hh24:mi:ss');

 RECORD_ID START_TI SOMETHING_ELSE
---------- -------- --------------------
         1 01-01-06 Test

1 rij is geselecteerd.


Re: datetime format [message #199593 is a reply to message #199583] Wed, 25 October 2006 03:55 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
dohhh........

didn't notice this before. You have 'mm' in your mask for minutes. change it to 'mi'.
Re: datetime format [message #199595 is a reply to message #199593] Wed, 25 October 2006 03:59 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

i already pointout "mm" to "mi". in my previous post

any way. problem is solved.
Re: datetime format [message #199596 is a reply to message #199583] Wed, 25 October 2006 04:00 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
Obviously the OP didn't notice?

I'll leave it to deni_sa to decide if his/her problem is solved.
Re: datetime format [message #199598 is a reply to message #199595] Wed, 25 October 2006 04:02 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
i change it , mm to mi but the error is the same im attaching you a print screen
Re: datetime format [message #199599 is a reply to message #199598] Wed, 25 October 2006 04:03 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

can you just copy n paste ur sql query output.

[Updated on: Wed, 25 October 2006 04:04]

Report message to a moderator

Re: datetime format [message #199601 is a reply to message #199593] Wed, 25 October 2006 04:08 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
see the file attachted
Re: datetime format [message #199603 is a reply to message #199592] Wed, 25 October 2006 04:10 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
No Message Body
  • Attachment: forum.rar
    (Size: 69.38KB, Downloaded 242 times)
Re: datetime format [message #199608 is a reply to message #199583] Wed, 25 October 2006 04:20 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
Ok. I hope I got all of them.

- space before between
- change hh:mm:ss to hh:mi:ss (twice)
- remove ',' in 'dd-mm-yyyy','hh:mi:ss'
- use the between only in the where clause.
Re: datetime format [message #199612 is a reply to message #199608] Wed, 25 October 2006 04:30 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
i'm becoming mad !!!

the new query
SELECT TO_CHAR (TRUNC (((initial_start_time_timestamp)+(initial_start_time_time_offset)/3600/24)), 'yyyy.mm.dd') , COUNT (DISTINCT cust_info_contract_id)
FROM udr_sep_2006
WHERE tariff_info_sncode = 1
GROUP BY ((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) BETWEEN TO_DATE ('04-sep-2006 23:00:00', 'dd-mon-yy hh24:mi:ss') AND
TO_DATE ('05-may-2006 6:00:00', 'dd-mon-yy hh24:mi:ss')
ORDER BY ((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) BETWEEN TO_DATE ('04-sep-2006 ,23:00:00', 'dd-mon-yy hh24:mi:ss') AND
TO_DATE ('05-may-2006 6:00:00', 'dd-mon-yy hh24:mi:ss');


the error --------------



ORA-00933 SQL command not properly ended

Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order.
Re: datetime format [message #199613 is a reply to message #199583] Wed, 25 October 2006 04:32 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
Don't get mad. That's very bad for your health Cool

You're almost there. End the query after
ORDER BY ((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24)

Oops. also remove the between in the group by.

SELECT TO_CHAR (TRUNC (((initial_start_time_timestamp)+(initial_start_time_time_offset)/3600/24)), 'yyyy.mm.dd') , COUNT (DISTINCT cust_info_contract_id)
FROM udr_sep_2006
WHERE tariff_info_sncode = 1
GROUP BY TO_CHAR (TRUNC (((initial_start_time_timestamp)+(initial_start_time_time_offset)/3600/24)), 'yyyy.mm.dd')
ORDER BY ((initial_start_time_timestamp)+(initial_start_time_time_offset)/3600/24)

[Updated on: Wed, 25 October 2006 04:34]

Report message to a moderator

Re: datetime format [message #199615 is a reply to message #199583] Wed, 25 October 2006 04:37 Go to previous messageGo to next message
mahendramahendra
Messages: 6
Registered: October 2006
Junior Member

BETWEEN can't be used in "GROUP BY" and "ORDER BY" clause... it is used in "WHERE" condition.

There is no meaning in doing "GROUP BY" from x date to y date... it is basically in where condition where you say include only data from x date to y date..

Also same with ORDER BY... in order by just give the column name based on which you want to order the output, not the condition.

[Updated on: Wed, 25 October 2006 04:39]

Report message to a moderator

icon8.gif  Re: datetime format [message #199618 is a reply to message #199613] Wed, 25 October 2006 04:44 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Razz Thank you Alien ,


Thank you to all you guys .Finally we make it executable but let see what result i'll take Smile .

Thank you again for the moment , maybe we will talk again ...Sad


Kisses from me ( that i'm a "she")
Re: datetime format [message #199626 is a reply to message #199618] Wed, 25 October 2006 09:38 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Good luck for next time.
Time format .......Helpp!! Again [message #199769 is a reply to message #199583] Thu, 26 October 2006 03:04 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Hi guys ,

----------
How can i get from one query :
-----------------------------------------------------------
SELECT COUNT(cust_info_contract_id), COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount),tariff_info_tmcode
FROM udr_sep_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
AND ((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) BETWEEN TO_DATE ('04-sep-2006 07:00:00', 'dd-mon-yy hh24:mi:ss') AND
TO_DATE ('04-sep-2006 22:00:00', 'dd-mon-yy hh24:mi:ss')
AND tariff_info_tmcode IN (58,59,61,62,63)
GROUP BY tariff_info_tmcode;
----------------------------------------------------------------
only this hours from 7:00 in the morning till 22;00 evening for all the month without executing it for every day , and without weekends .

So for september the query will select the data from this days{1sep,4,5,6,7,8,11,12,13,ect and the hours must be between 7-22


time format [message #199776 is a reply to message #199626] Thu, 26 October 2006 03:25 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Hi guys ,

----------
How can i get from one query :
-----------------------------------------------------------
SELECT COUNT(cust_info_contract_id), COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount),tariff_info_tmcode
FROM udr_sep_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
AND ((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) BETWEEN TO_DATE ('04-sep-2006 07:00:00', 'dd-mon-yy hh24:mi:ss') AND
TO_DATE ('04-sep-2006 22:00:00', 'dd-mon-yy hh24:mi:ss')
AND tariff_info_tmcode IN (58,59,61,62,63)
GROUP BY tariff_info_tmcode;
----------------------------------------------------------------
only this hours from 7:00 in the morning till 22;00 evening for all the month without executing it for every day , and without weekends .

So for september the query will select the data from this days{1sep,4,5,6,7,8,11,12,13,ect and the hours must be between 7-22
Re: datetime format [message #199783 is a reply to message #199583] Thu, 26 October 2006 03:38 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
Split it in 2 parts. One for the weekdays, and another for the time:

Time:
and to_char((initial_start_time_timestamp)+(initial_start_time_time_offset)/3600/24,'HH24') between 7 and 22 


Weekdays:
and to_char((initial_start_time_timestamp)+(initial_start_time_time_offset)/3600/24,'DY')
not in (to_char( to_date('20050101', 'yyyymmdd' ), 'DY' ), 
        to_char( to_date('20050102', 'yyyymmdd' ), 'DY' ) )

Re: datetime format [message #199784 is a reply to message #199583] Thu, 26 October 2006 03:41 Go to previous messageGo to next message
mahendramahendra
Messages: 6
Registered: October 2006
Junior Member
SELECT COUNT(cust_info_contract_id), COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount),tariff_info_tmcode
FROM udr_sep_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) ,'Mon-YYYY') = 'Sep-2006'
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24') between 7 and 22
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) ,'DY') not in ('SAT','SUN')
AND tariff_info_tmcode IN (58,59,61,62,63)
GROUP BY tariff_info_tmcode;

[Updated on: Thu, 26 October 2006 03:50]

Report message to a moderator

Re: datetime format [message #199786 is a reply to message #199784] Thu, 26 October 2006 03:47 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
ORA-01481 invalid number format model

Cause: An invalid format parameter was used with the TO_CHAR or TO_NUMBER function.

Action: Correct the syntax, then retry the operation.

Re: datetime format [message #199789 is a reply to message #199583] Thu, 26 October 2006 03:50 Go to previous messageGo to next message
mahendramahendra
Messages: 6
Registered: October 2006
Junior Member


changed the above query from to_number to to_char... try now.. sorry.
Re: datetime format [message #199792 is a reply to message #199789] Thu, 26 October 2006 03:56 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
is being executed , let see the result
icon14.gif  Re: datetime format [message #199796 is a reply to message #199583] Thu, 26 October 2006 04:04 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
A big THANK YOU !!!
Re: datetime format [message #199815 is a reply to message #199783] Thu, 26 October 2006 05:59 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
What if i want for hours 22-til 6 in the morning ?

SELECT COUNT(cust_info_contract_id), COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount),tariff_info_tmcode
FROM udr_sep_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
and call_type=1
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) ,'Mon-YYYY') = 'Sep-2006'
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24') between 7 and 22
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) ,'DY') not in ('SAT','SUN')
AND tariff_info_tmcode IN (58,59,61,62,63)
GROUP BY tariff_info_tmcode;
Re: datetime format [message #199818 is a reply to message #199583] Thu, 26 October 2006 06:21 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
Would this work?

SELECT COUNT(cust_info_contract_id), COUNT(uds_record_id), SUM(duration_volume)/60, SUM(rated_flat_amount),tariff_info_tmcode
FROM udr_sep_2006
WHERE tariff_info_sncode =1
AND xfile_ind ='H'
and call_type=1
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) ,'Mon-YYYY') = 'Sep-2006'
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24')-7 between 15 and 23
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24) ,'DY') not in ('SAT','SUN')
AND tariff_info_tmcode IN (58,59,61,62,63)
GROUP BY tariff_info_tmcode; 
Re: datetime format [message #199820 is a reply to message #199818] Thu, 26 October 2006 06:51 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Alien Sorry but i don't understand the logic of this part
....)-7 between 15 and 23

Deni
Re: datetime format [message #199821 is a reply to message #199583] Thu, 26 October 2006 06:55 Go to previous messageGo to next message
Alien
Messages: 236
Registered: June 1999
Senior Member
Smile It's a trick.

Since there is a day difference between 22 and 6, it would be easiest to 'shift' the window. If you subtract more than 6 hours from the time, it will end up in the same day. Then you can use a between again.
Re: datetime format [message #199826 is a reply to message #199820] Thu, 26 October 2006 07:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Quote:
AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24') between 7 and 22


You cannot/should not compare a char with numbers like that; you have to wrap it in a to_number
Re: datetime format [message #199828 is a reply to message #199826] Thu, 26 October 2006 07:07 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
I try it Frank to_number and it gives error .

But the problem now is how to select the hours from 22 to 6 in the morning .
Re: datetime format [message #199831 is a reply to message #199828] Thu, 26 October 2006 07:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
deni_sa wrote on Thu, 26 October 2006 14:07
I try it Frank to_number and it gives error .

Show me.
Re: datetime format [message #199833 is a reply to message #199821] Thu, 26 October 2006 07:14 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
Alien Sad i still don't get it , pleasee
Re: datetime format [message #199837 is a reply to message #199831] Thu, 26 October 2006 07:27 Go to previous messageGo to next message
deni_sa
Messages: 77
Registered: June 2006
Member
For Frank


The error it shows

ORA-01481 invalid number format model

Cause: An invalid format parameter was used with the TO_CHAR or TO_NUMBER function.

Action: Correct the syntax, then retry the operation.
Re: datetime format [message #199838 is a reply to message #199833] Thu, 26 October 2006 07:27 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

AND to_char(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24')-7 between 15 and 23
 instead of 
Just try this way



AND to_number(((initial_start_time_timestamp) +(initial_start_time_time_offset)/3600/24),'HH24')-7 between 15 and 23

[Updated on: Thu, 26 October 2006 07:28]

Report message to a moderator

Re: datetime format [message #199841 is a reply to message #199838] Thu, 26 October 2006 07:32 Go to previous messageGo to previous message
deni_sa
Messages: 77
Registered: June 2006
Member
what if i use not between 7and 21 is correct as a syntax ?
Previous Topic: How to get date in words like July 9, 2003 using to_date function
Next Topic: break command
Goto Forum:
  


Current Time: Sun Dec 28 07:08:01 CST 2014

Total time taken to generate the page: 0.23044 seconds