Problem with Date/time in Oracle 1

[malaygal]

I am really having major difficulty with date/time in Oracle.
I just imported an Excel file to Oracle. On the excel file, I have a date column with format mm/dd/yyyy hh:mi (6/27/2007 18:05)

When I imported the excel file (using Oracle SQL developer), I masked the date as mm-dd-yyyy hh:mi.
It imported fine, but the column in Oracle shows as 27-Jun-07.

I have an asp page that will eventually update this date with this statement:

Date_Sub = Now()

strsql = "INSERT INTO tblfinal (Region, Date_Submitted) VALUES ('" & region & "',TO_TIMESTAMP('" & Date_Sub & "','MM/DD/YYYY HH:MI:SS'))"

Comm.commandtype = 1
Comm.commandtext = strsql1

Comm.Execute

Comm.commandtext = strsql
Comm.Execute

This generates an error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-01830: date format picture ends before converting entire input string

I have another page the will display this column with this:

<tr><td align=left><FONT FACE="Arial" SIZE=2><%=trim(rs("Region"))%></td>
<td align=left><FONT FACE="Arial" SIZE=2><%=FormatDateTime(rs("Date_Submitted"),vbLongDate)%></td>

The first column of the first record was diplayed but got this error on the second column:

error '80020009'

I have googled all these error and am not even close to resolving them.
Any help will be greatly appreciated.

 

[Turkbear]

Hi,
Oracle displays a DATE (or TIMESTAMP) field based on thr NLS_DATE_FORMAT setting in effect or in the Default Oracle format, DD-MON-YY.

This has nothing to do with how it is stored, of course

As to the asp error, add a Response.Write(sqlstr) step before actually sending the string to the database.Check to see if the correct format is being sent..

TO_TIMESTAMP(<somestring>,'MM/DD/YYYY HH:MI:SS')

Check for the quotes...



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[malaygal]

Thanks for the quick reply.

I did a response.write(strsql) and got this:

INSERT INTO tblfinal (Region, Date_Submitted) VALUES ('New England Region',TO_TIMESTAMP('6/27/2007 3:46:25 PM','MM/DD/YYYY HH:MI:SS'))

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-01830: date format picture ends before converting entire input string

 

[SantaMufasa]

MalayGal,

Change your code to read:

strsql  = "INSERT INTO tblfinal (Region, Date_Submitted) VALUES ('" & region & "',TO_TIMESTAMP('" & Date_Sub & "','MM/DD/YYYY HH:MI:SS AM'))"

You can use either "AM" or "PM" in your data-format mask and it will take care of your date properly.

Let us know your findings.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[BJCooperIT]

I am concerned about the use of

'MM/DD/YYYY HH:MI:SS'

.

Shouldn't that have been

'MM/DD/YYYY HH[b]24[/b]:MI:SS'

?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[Turkbear]

Hi,
Depends on what you want the time to display:

13:00:12 or 1:00:12 AM





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[BJCooperIT]

True, but malaygal said it was used when she "imported the excel file".

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[SantaMufasa]

Malaygal's incoming DATE/TIME data apparently are in the form '6/27/2007 3:46:25 PM', which require the 'AM/PM' designator.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]