OraFAQ Forum: SQL & PL/SQL » Working with Dates in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Working with Dates in Oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Working with Dates in Oracle [message #248443]

Fri, 29 June 2007 01:41

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248446 is a reply to message #248443]

Fri, 29 June 2007 01:45

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Working with Dates in Oracle [message #248461 is a reply to message #248446]

Fri, 29 June 2007 02:14

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

nadeemraza092
Messages: 14
Registered: June 2007
Location: Pakistan

Junior Member

Sir Form's Screen is attached

Attachment: untitled.bmp
(Size: 423.27KB, Downloaded 471 times)

Report message to a moderator

Re: Working with Dates in Oracle [message #248487 is a reply to message #248443]

Fri, 29 June 2007 03:21

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248508 is a reply to message #248487]

Fri, 29 June 2007 04:47

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

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248522 is a reply to message #248513]

Fri, 29 June 2007 06:23

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248525 is a reply to message #248522]

Fri, 29 June 2007 06:38

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248528 is a reply to message #248525]

Fri, 29 June 2007 07:01

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

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248530 is a reply to message #248528]

Fri, 29 June 2007 07:17

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248533 is a reply to message #248530]

Fri, 29 June 2007 07:31

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

Report message to a moderator

Re: Working with Dates in Oracle [message #248534 is a reply to message #248533]

Fri, 29 June 2007 07:39

Maaher
Messages: 7065
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

Wink

MHE

Report message to a moderator

Re: Working with Dates in Oracle [message #248535 is a reply to message #248533]

Fri, 29 June 2007 07:41

joy_division
Messages: 4963
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.

Report message to a moderator

Re: Working with Dates in Oracle [message #248536 is a reply to message #248535]

Fri, 29 June 2007 07:43

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

Report message to a moderator

Previous Topic:	creating external table problem
Next Topic:	max length of the date function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 09 20:03:44 CST 2024