Home » SQL & PL/SQL » SQL & PL/SQL » Updating Date value with Date Time
Updating Date value with Date Time [message #186528] Tue, 08 August 2006 06:16 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I want to update the Date value in a table with Date and Time.

I am using the following sql statement for updation, but getting the error.

update gp_ia_transaction_header set trans_dts=to_char(sysdate,'DD-MON-YYYY HH:MI')

SQL> /

update gp_ia_transaction_header set trans_dts=to_char(sysdate,'DD-MON-YYYY HH:MI')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string




Can any one help me in this.

Thanks,

Nirmal
Re: Updating Date value with Date Time [message #186529 is a reply to message #186528] Tue, 08 August 2006 06:20 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

just try this.
update gp_ia_transaction_header set trans_dts=sysdate




regards,
Re: Updating Date value with Date Time [message #186530 is a reply to message #186528] Tue, 08 August 2006 06:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Can you describe "gp_ia_transaction_header"?

MHE
Re: Updating Date value with Date Time [message #186531 is a reply to message #186530] Tue, 08 August 2006 06:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Look at this script to see what I'm after:
ALTER SESSION SET nls_date_format='DD-MON-YY'
/

CREATE TABLE mhe_foo(id NUMBER, col1 VARCHAR2(20), col2 DATE)
/

PROMPT DATE to VARCHAR2 conversion goes well for col1
INSERT INTO mhe_foo VALUES(1,sysdate, sysdate)
/

PROMPT VARCHAR2 to DATE conversion results in an error if the
PROMPT default date format is wrong
INSERT INTO mhe_foo VALUES(2
                          ,to_char(sysdate,'DD-MON-YYYY HH:MI')
                          ,to_char(sysdate,'DD-MON-YYYY HH:MI')
                          )
/

PROMPT Let's change the date format and retry
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH:MI'
/

INSERT INTO mhe_foo VALUES(3
                          ,to_char(sysdate,'DD-MON-YYYY HH:MI')
                          ,to_char(sysdate,'DD-MON-YYYY HH:MI')
                          )
/

SELECT *
FROM   mhe_foo
/

DROP TABLE mhe_foo
/
If I run it, I get this result:
SQL> @orafaq

Session altered.


Table created.

DATE to VARCHAR2 conversion goes well for col1

1 row created.

VARCHAR2 to DATE conversion results in an error if the
default date format is wrong
                          ,to_char(sysdate,'DD-MON-YYYY HH:MI')
                           *
ERROR at line 3:
ORA-01830: date format picture ends before converting entire input string


Let's change the date format and retry

Session altered.


1 row created.


        ID COL1                 COL2
---------- -------------------- -----------------
         1 08-AUG-06            08-AUG-2006 01:24
         3 08-AUG-2006 01:24    08-AUG-2006 01:24


Table dropped.

SQL>


MHE
Re: Updating Date value with Date Time [message #186550 is a reply to message #186531] Tue, 08 August 2006 08:11 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Rememer this one fact about Oracle dates and all should become much clearer: ORACLE DATES ARE SAVED AS FLOATING POINT NUMBERS! The portion on the LEFT of the decimal represents the date. The portion on the RIGHT of the decimal represents the time. YOU NEVER SEE THIS NUMBER! The statement "ALTER SESSION SET nls_date_format='DD-MON-YY'" tells your session how to translate the date.time number into something you can read. There is also the "to_char()" command which will take a date and format it into what you want to see. Try this...

to_char(sysdate,'MM/DD/YYYY');
to_char(sysdate,'DD-MON-YYYY');
to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
to_char(sysdate,'Mon fmddth, YYYY HH:MI:SS am');


Every one of these statements take the current date.time number from Oracle and format it into something you can read.

Now, fact #2 about Oracle dates: EVERY DATE FIELD HAS BOTH DATE AND TIME ASSOCIATED WITH IT. That is, both the date and the time value are _ALWAYS_ there. If you save a value in a date field like this: to_date('08/10/2006','MM/DD/YYYY'), the time portion of the date.time number is 000000000000 which corresponds to midnight of the date entered.

You should always compare a DATE with a DATE and always remember that to_char(sysdate,'MM/DD/YYYY') results in a STRING, not a DATE type.

Good luck with your date.time manipulations.

Ron
Previous Topic: OraOleDBpus.dll not found? Help
Next Topic: last day of every month
Goto Forum:
  


Current Time: Sun Dec 11 02:12:36 CST 2016

Total time taken to generate the page: 0.07567 seconds