Home » SQL & PL/SQL » SQL & PL/SQL » Working with Dates in Oracle
icon2.gif  Working with Dates in Oracle [message #248443] Fri, 29 June 2007 01:41 Go to next message
nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan
Junior Member

Dear freinds:

I have Report where I have three columns

Like

Entry Date Time in Time Out TOTAL HOURS
----------------------------------------------------
03-APR-07 08:19 16:32
04-APR-07 08:19 16:34

BY SOME MISTAKE THE DATE FEEDED IN THE TIMEOUT COLUMN IS UPDATED TO 01-JUN-07

NOW WHEN I ADD THE NEW FORMULA COLUMN TOTAL HOURS AT THE REPORT THE RESULT OF TIME OUT DISTURBS THE RESULT OF TOTAL HOURS BECAUSE DATE OF ENTRY DATE AND TIME IN DATE IS OF APRIL MONTH.

WHAT CAN I DO IN THIS SITUATION

--> IS IT POSSIBLE TO UPDATE ONLY DATE 'DD-MON-RR' OF TIME OUT FIELD WITH THE ENTRY DATE
IF YES THEN PLEASE TELL ME THE WAY.

PLEASE REPLY SOON


NADEEM RAFIQ
Re: Working with Dates in Oracle [message #248446 is a reply to message #248443] Fri, 29 June 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PLEASE KEEP OFF YOR FINGER FROM SHIFT key.

Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

What the datatype of your columns?
What did you do?
Copy and paste your screen.

Your get an answer as fast as you answer this.

Regards
Michel
Working with Dates in Oracle [message #248461 is a reply to message #248446] Fri, 29 June 2007 02:14 Go to previous messageGo to next message
nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan
Junior Member

SQL> desc attendance;
 Name                            Null?    Type
 ------------------------------- -------- ----
 REC_ID                          NOT NULL VARCHAR2(5)
 EMP_ID                                   VARCHAR2(7)
 ENTERY_DATE                              DATE
 GATE_TIME_IN                             DATE
 GATE_TIME_OUT                            DATE
 TIME_IN                                  DATE
 TIME_OUT                                 DATE
 ENT_DATE                                 DATE
 IP_ADDRESS                               VARCHAR2(15)
 OS_USER                                  VARCHAR2(15)
 OS_TERM                                  VARCHAR2(15)
 USERID                                   VARCHAR2(15)
Sir this is My Table Structure and In Time_in and Time_out Column I have stored Time also but in the Entrery_date column Only Date is Stored

[Updated on: Fri, 29 June 2007 04:55]

Report message to a moderator

Re: Working with Dates in Oracle [message #248464 is a reply to message #248443] Fri, 29 June 2007 02:26 Go to previous messageGo to next message
nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan
Junior Member

Sir Form's Screen is attached


  • Attachment: untitled.bmp
    (Size: 423.27KB, Downloaded 135 times)
Re: Working with Dates in Oracle [message #248487 is a reply to message #248443] Fri, 29 June 2007 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts

Quote:
IS IT POSSIBLE TO UPDATE ONLY DATE 'DD-MON-RR' OF TIME OUT FIELD WITH THE ENTRY DATE

Yes, use UPDATE statement. But As we don't have what are you real data, where is the error, and so on, we can't say more.

Regards
Michel
Re: Working with Dates in Oracle [message #248508 is a reply to message #248487] Fri, 29 June 2007 04:47 Go to previous messageGo to next message
nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan
Junior Member

Dear Sir;

Please check this query which I used to Update the Date in the Time_out column

UPDATE ATTENDANCE
SET TIME_OUT = TO_DATE(TO_CHAR(ENTERY_DATE,'DD-MON-RRRR'))||' '||TO_CHAR(TIME_OUT,'HH24.MI')
WHERE EMP_ID='3190017'
AND ENTERY_DATE BETWEEN '01-APR-07' AND '30-APR-07'


It returns following error message.

ORA-01843: not a valid month

Now I think you will understand what I want to do. Please see the table structure before reading this query. Infact I want that the Column Time_out should updated by the date feeded in Column Entery_date and Time whcih is feeded in the Time_out Column remains the same.

Nadeem

[Updated on: Fri, 29 June 2007 04:52]

Report message to a moderator

Re: Working with Dates in Oracle [message #248513 is a reply to message #248508] Fri, 29 June 2007 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ One of your parenthesis is misplaced
2/ You have to give a format in TO_DATE
3/ You have to use TO_DATE function in where clause

Regards
Michel
Re: Working with Dates in Oracle [message #248522 is a reply to message #248513] Fri, 29 June 2007 06:23 Go to previous messageGo to next message
nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan
Junior Member

Sir Can you Please Post the complete Query what are you saying

I mean Please Send the Original Query.

I shall be very thankful to you

Nadeem
Re: Working with Dates in Oracle [message #248525 is a reply to message #248522] Fri, 29 June 2007 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix your query as I said.
You will learn more if you do it yourself than if I do.

Regards
Michel
Re: Working with Dates in Oracle [message #248528 is a reply to message #248525] Fri, 29 June 2007 07:01 Go to previous messageGo to next message
nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan
Junior Member

Sir I have Tried it as

UPDATE ATTENDANCE
SET TIME_OUT = TO_DATE('DD-MON-RRRRHH24.MISS',TO_CHAR(ENTERY_DATE,'DD-MON-RRRR')||TO_CHAR(TIME_OUT,'HH24.MI'))
WHERE EMP_ID='3190017'
AND TO_DATE(ENTERY_DATE) BETWEEN '01-APR-07' AND '30-APR-07'


But Now It Returns

ERROR at line 2:
ORA-01821: date format not recognized

Sir please Help Me out

Nadeem

[Updated on: Fri, 29 June 2007 07:03]

Report message to a moderator

Re: Working with Dates in Oracle [message #248529 is a reply to message #248528] Fri, 29 June 2007 07:14 Go to previous messageGo to next message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

I think this would help you better

http://www.techonthenet.com/oracle/functions/to_date.php
Re: Working with Dates in Oracle [message #248530 is a reply to message #248528] Fri, 29 June 2007 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For the SET line reread my point 2.
For the WHERE line, you don't apply TO_DATE on a date, you have to apply it on...


Regards
Michel
Re: Working with Dates in Oracle [message #248533 is a reply to message #248530] Fri, 29 June 2007 07:31 Go to previous messageGo to next message
nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan
Junior Member

Sir Its Top Urgent Please Solve My Problem this time

It is necessary in our companies audit Reports


Thanks

Nadeem
Re: Working with Dates in Oracle [message #248534 is a reply to message #248533] Fri, 29 June 2007 07:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You would use TO_DATE to convert a CHARACTER string TO a DATE (hence the name: TO_DATE). Now, in your where clause, do you use string literals? Yes you do. Do you want to make them DATEs? Yes you do. Why? Because you want to compare those strings to a date column (ENTERY_DATE). Making ENTERY_DATE a DATE(through the TO_DATE) doesn't make sense. Oracle will first convert it to a varchar2(internally) and convert it back to a date through TO_DATE. You end up with the same date you started with. So move the TO_DATE to the other part of your equation.

TO_DATE works like this:
TO_DATE(character_value, date_format)
Not like you did.
TO_DATE(date_format, character_value)


Oh, before I forget: it is not urgent and it does not have top priority. At least, not to me. Wink

MHE
Re: Working with Dates in Oracle [message #248535 is a reply to message #248533] Fri, 29 June 2007 07:41 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
nadeemraza092 wrote on Fri, 29 June 2007 08:31
Sir Its Top Urgent Please Solve My Problem this time



The word urgent will cause more sarcastic remarks, more people to ignore future posts by you and keep you running around in circles. We don't care if you have an urgent matter. If it's so urgent, pay a consultant $200 per hour to solve it for you.
Re: Working with Dates in Oracle [message #248536 is a reply to message #248535] Fri, 29 June 2007 07:43 Go to previous message
caliguardo
Messages: 107
Registered: February 2007
Location: Chennai
Senior Member

Are you the only one in your company for Oracle?. This is just a forum and there is nothing urgent or Ordinary for us
Previous Topic: creating external table problem
Next Topic: max length of the date function
Goto Forum:
  


Current Time: Thu Dec 08 02:02:12 CST 2016

Total time taken to generate the page: 0.27766 seconds