RE: inserting into DATE colum
It's actually: SQL select To_Date('03/09/2001','MM-DD-'), 2 To_Date('03/09/2001','MM/DD/'), 3 To_Date('03/09/2001 11:11:11','MM/DD/ HH:MI:SS') 4 from dual; TO_DATE(' TO_DATE(' TO_DATE('- - -09-MAR-01 09-MAR-01 09-MAR-01 SQL ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD-HH24.MI.SS'; Session altered. SQL select To_Date('03/09/2001','MM-DD-'), 2 To_Date('03/09/2001','MM/DD/'), 3 To_Date('03/09/2001 11:11:11','MM/DD/ HH:MI:SS') 4 from dual; TO_DATE('03/09/2001 TO_DATE('03/09/2001 TO_DATE('03/09/2001--- --- ---2001-03-09-00.00.00 2001-03-09-00.00.00 2001-03-09-11.11.11 SQL ALTER SESSION SET NLS_DATE_FORMAT = '-MM-DD'; Session altered. SQL select To_Date('03/09/2001','MM-DD-'), 2 To_Date('03/09/2001','MM/DD/'), 3 To_Date('03/09/2001 11:11:11','MM/DD/ HH:MI:SS') 4 from dual; TO_DATE('0 TO_DATE('0 TO_DATE('0-- -- --2001-03-09 2001-03-09 2001-03-09 Wendy William Beilstein [EMAIL PROTECTED] wrote: A database table date column always stores the date and time. For example is the current date and time was 09-apr-2001 at 14:23.32 .INSERT INTO MY_TABLE(MY_DATE) VALUES(SYSDATE);This would store the entire date and time.INSERT INTO MY_TABLE(MY_DATE) VALUES(TRUNC(SYSDATE));This would store the date with a time of 00:00.00;If you want to insert a date that is formatted in a varchar2 field into a date field you would do the following.insert into TbTABLE values (To_Date('03/09/2001','MM-DD-'), To_Date('03/09/2001','MM/DD/'),To_Date('03/09/2001 11:11:11','MM/DD/ HH:MI:SS)) [EMAIL PROTECTED] 04/09/01 02:26PM Helmut,If you want to insert the date in any format other than 'DD-MON-' or'DD-MON-YY', you need to specify the date format when you use the to_datefunction.! ! Prakash-Original Message-Sent: Monday, April 09, 2001 2:01 PMTo: Multiple recipients of list ORACLE-LHi!I have a question concerning inserting data in a DATE column. The deal isthat sometimes, I just wanna insert DD/MM/ and in other cases I wannainsert DD/MM/ HH:MI:SS in the same column.How can I handle that?insert into TbTABLE values (To_Date('03/09/2001'), To_Date('03/09/2001'),To_Date('03/09/2001'), To_Date('03/09/2001'),To_Date('03/09/2001 11:11:11'))Do I need to supply formatting information for the date to the insertstatement?This is 8.1.6 on Win2k.Thanks,Helmut-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut DaimingerINET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing Lists! ! To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, PrakashINET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you w! ! ant to be removed from). You mayalso send the HELP command for other information (like subscribing).--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: William BeilsteinINET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).Do You Yahoo!? Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.
RE: inserting into DATE colum
You only need format for the HH:MI:SS not the others. Rick -Original Message- From: Helmut Daiminger [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 09, 2001 2:01 PM To: Multiple recipients of list ORACLE-L Subject: inserting into DATE colum Hi! I have a question concerning inserting data in a DATE column. The deal is that sometimes, I just wanna insert DD/MM/ and in other cases I wanna insert DD/MM/ HH:MI:SS in the same column. How can I handle that? insert into TbTABLE values (To_Date('03/09/2001'), To_Date('03/09/2001'), To_Date('03/09/2001'), To_Date('03/09/2001'), To_Date('03/09/2001 11:11:11')) Do I need to supply formatting information for the date to the insert statement? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: inserting into DATE colum
A database table date column always stores the date and time. For example is the current date and time was 09-apr-2001 at 14:23.32 . INSERT INTO MY_TABLE(MY_DATE) VALUES(SYSDATE); This would store the entire date and time. INSERT INTO MY_TABLE(MY_DATE) VALUES(TRUNC(SYSDATE)); This would store the date with a time of 00:00.00; If you want to insert a date that is formatted in a varchar2 field into a date field you would do the following. insert into TbTABLE values (To_Date('03/09/2001','MM-DD-'), To_Date('03/09/2001','MM/DD/'), To_Date('03/09/2001 11:11:11','MM/DD/ HH:MI:SS)) [EMAIL PROTECTED] 04/09/01 02:26PM Helmut, If you want to insert the date in any format other than 'DD-MON-' or 'DD-MON-YY', you need to specify the date format when you use the to_date function. Prakash -Original Message- Sent: Monday, April 09, 2001 2:01 PM To: Multiple recipients of list ORACLE-L Hi! I have a question concerning inserting data in a DATE column. The deal is that sometimes, I just wanna insert DD/MM/ and in other cases I wanna insert DD/MM/ HH:MI:SS in the same column. How can I handle that? insert into TbTABLE values (To_Date('03/09/2001'), To_Date('03/09/2001'), To_Date('03/09/2001'), To_Date('03/09/2001'), To_Date('03/09/2001 11:11:11')) Do I need to supply formatting information for the date to the insert statement? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: William Beilstein INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: inserting into DATE colum
Hmmm Whatever format you insert date into the date column, it is stored with time portion as well. The format mask would be of use when you RETRIEVE from the database. HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! * This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).