Home » SQL & PL/SQL » SQL & PL/SQL » query (Oracle 10g)
query [message #393272] Sat, 21 March 2009 01:13 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,
iam having a quetu wich is displaying results for the below one like this
SELECT AML.MODULE_ID,MET.MODULE_NAME,AML.EVENT_ID,AEL.EVENT_NAME, 
     TO_CHAR(AML.DATE_TIME,'MM-DD-YYYY HH:MI AM') AS DATE_TIME,
  AML.USER_LOGIN,AML.PATIENT_ID,  AML.REFERENCE_ID, USERS.USER_NAME
 FROM EMRAuditTrial AML  
 INNER JOIN EMRAuditEventLkup AEL ON AEL.EVENT_ID = AML.EVENT_ID INNER JOIN EMRAuditModuleLkup  MET ON MET.MODULE_ID = AML.MODULE_ID INNER JOIN USERS ON AML.USER_LOGIN = USERS.USER_LOGIN  WHERE  AML.DATE_TIME BETWEEN '15-MAR-2009'  AND '16-MAR-2009'  AND  AML.PATIENT_ID = '1682'
 output is:
 1002	Problems	1002	Edit	03-15-2009 12:23 PM	1518_Ora1	1682	1588	Ora
1002	Problems	1001	View	03-15-2009 01:16 PM	1518_e	1682	1588	E
1002	Problems	1001	View	03-15-2009 12:23 PM	1518_Ora1	1682	1588	Ora
1002	Problems	1001	View	03-15-2009 12:25 PM	1518_a	1682	1588	A
1003	Allergies	1003	Add	03-15-2009 12:22 PM	1518_Ora1	1682	1602	Ora

wjen i five my query like this it is ot displaying results

SELECT AML.MODULE_ID,MET.MODULE_NAME,AML.EVENT_ID,AEL.EVENT_NAME, 
     TO_CHAR(AML.DATE_TIME,'MM-DD-YYYY HH:MI AM') AS DATE_TIME,
  AML.USER_LOGIN,AML.PATIENT_ID,  AML.REFERENCE_ID, USERS.USER_NAME
 FROM EMRAuditTrial AML  
 INNER JOIN EMRAuditEventLkup AEL ON AEL.EVENT_ID = AML.EVENT_ID INNER JOIN EMRAuditModuleLkup  MET ON MET.MODULE_ID = AML.MODULE_ID INNER JOIN USERS ON AML.USER_LOGIN = USERS.USER_LOGIN  WHERE  AML.DATE_TIME BETWEEN '15-MAR-2009 00.00 A.M'  AND '15-MAR-2009 12:59 P.M'  AND  AML.PATIENT_ID = '1682'

please help me out for geetinf results

error like: ORA-01830: date format picture ends before converting entire input string



[Updated on: Sat, 21 March 2009 01:25]

Report message to a moderator

Re: query [message #393277 is a reply to message #393272] Sat, 21 March 2009 01:22 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>please help me out for geetinf results
You have results.
What is the problem?
Re: query [message #393280 is a reply to message #393277] Sat, 21 March 2009 01:31 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
ACTUALLY I NEEDED RECORDS ON 15-MAR-2009 FROM 12:00 AM TO 12:OO PM
but i am uaable to get it through date format.
like how to specify 15-MAR-2009 OO:00 AM TO 15-MAR-2009 12:59 PM.
how to handle it
Re: query [message #393283 is a reply to message #393272] Sat, 21 March 2009 01:35 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
with Oracle characters between single quote marks are strings.
'this is a string'
'2009-03-20' -- this is also a string
TO_DATE() function is used to convert strings to DATE data types.
Re: query [message #393286 is a reply to message #393283] Sat, 21 March 2009 01:40 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can you give me the query for better results please

hi how to include to_date functionality in the above query for getting desired results

[Updated on: Sat, 21 March 2009 01:44]

Report message to a moderator

Re: query [message #393288 is a reply to message #393272] Sat, 21 March 2009 01:43 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>can you give me the query for better results please
Better?
Better than what?
This is your data & results.
I do not know which results are better.
Re: query [message #393292 is a reply to message #393272] Sat, 21 March 2009 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: query [message #393293 is a reply to message #393288] Sat, 21 March 2009 01:48 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i tried out with to_date also it is not working my quey seems no problem what i want is to get the records on a particular day with the above format been changed.there i am failing?
Re: query [message #393294 is a reply to message #393293] Sat, 21 March 2009 01:56 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
the one which i tried out is working fine with sql server why cant with oracle.can any one help me out in this? i am not that much capable in handling oracle things
Re: query [message #393295 is a reply to message #393294] Sat, 21 March 2009 02:20 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,using to_date also not helping my problem.can anyone just go for this.
Re: query [message #393303 is a reply to message #393295] Sat, 21 March 2009 04:16 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi,

can anyone give me the query for this.i have tried,tried and un anble to get so far.
Re: query [message #393314 is a reply to message #393303] Sat, 21 March 2009 06:38 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rajasekhar857 wrote on Sat, 21 March 2009 10:16
Hi,

can anyone give me the query for this.i have tried,tried and un anble to get so far.

Use TO_DATE instead of string literals as suggested before.

'15-MAR-2009' is string
TO_DATE( '15-MAR-2009', 'DD-MON-YYYY' ) is date
TO_DATE( '15-MAR-2009 12:00 AM', 'DD-MON-YYYY HH:MI AM' ) is also date

For all format modifiers, have a look into SQL Reference. It is available with other documentation books e.g. online on http://tahiti.oracle.com/.

Moreover, TO_DATE( '15-MAR-2009 00:00 AM', 'DD-MON-YYYY HH:MI AM' ) is not valid time specification. More info about 12-hour clock can be found e.g. on http://en.wikipedia.org/wiki/12-hour_clock#Criticism_and_practical_problems.
Previous Topic: error while insert
Next Topic: Error in code
Goto Forum:
  


Current Time: Tue Dec 06 02:44:37 CST 2016

Total time taken to generate the page: 0.15833 seconds