Oracle Help - inserting date and time in a supplement

All, I have a script for inserting and image with the date into the supplement and I want to add Time in the same field,  The field is Sup_added_dt  here is a copy of the string  I added HH:MI:SSAM to the 2 dates spots but it doesnt seem to work. Any Ideas Im not really the Orcale persone here just helping

 sqlString = " INSERT INTO SUPPLEMENT (case_id, image_id,sup_document,sup_text, sup_added_dt, sup_last_update_dt, sup_added_by_user_id, sup_last_updated_by_user_id) VALUES (" & caseId & ", " & newImgID & ", " & "'" & tempbarcode1 & "'" &"," & "'" & supptext & "'" &", " & "to_date('" & date & "','MM/DD/YYYY:hh:mi:ssam') " & ", " & "to_date('" & date & "','MM/DD/YYYY:hh:mi:ssam') " & "," & "467" & "," & "467" &");"
LVL 1
nelslarsonAsked:
Who is Participating?
 
nelslarsonConnect With a Mentor Author Commented:
We came up with a solution of removing TIME and using SYSDATE instead

here is the code that works now

sqlString = " INSERT INTO SUPPLEMENT (case_id, image_id,sup_document,sup_text, sup_added_dt, sup_last_update_dt, sup_added_by_user_id, sup_last_updated_by_user_id) VALUES (" & caseId & ", " & newImgID & ", " & "'" & tempbarcode1 & "'" &"," & "'" & supptext & "'" &", " & "SYSDATE " & ", " & "SYSDATE " & "," & "467" & "," & "467" &");"
0
 
lwadwellCommented:
What is the 'exact' format of the dates that will be in the variable 'date' ... your current to_date() has the format as matching 12/21/2009:11:05:04am ... do you really have the colon ':' between the year and the time?

If not, the format should be 'MM/DD/YYYY hh:mi:ssam'.
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
AS Iwadwell told the date format is be changed

then try........

i.e MM/DD/YYYY HH:MI:SS AM
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
nelslarsonAuthor Commented:
We are testing that now but for both my and you time still just comes up as 12:00:00am in the database.  I do notice the field is set to null could this be the reason?
0
 
lwadwellCommented:
What is the value in the date variable that is being used in the INSERT?  Please provide some examples.
0
 
nelslarsonAuthor Commented:
Information        ExtractBarcodeValue :  INSERT INTO SUPPLEMENT (case_id, image_id,sup_document,sup_text, sup_added_dt, sup_last_update_dt, sup_added_by_user_id, sup_last_updated_by_user_id) VALUES (77962, 254082, 'L-1420090421105003.tif','L-14', to_date('4/21/2009','MM/DD/YYYY hh:mi:ssam') , to_date('4/21/2009','MM/DD/YYYY hh:mi:ssam') ,467,467);          04/21/2009 10:50:03

 
0
 
lwadwellCommented:
nelslarson,

the value in the TO_DATE function has no time component.  It is "to_date('4/21/2009','MM/DD/YYYY hh:mi:ssam')" - this will leave the time as the default value ... 00:00:00 (which is 12:00:00am).

to insert the time as well ... the INSERT needs to have the time in the TO_DATE(), e.g. to_date('04/21/2009 10:50:03','MM/DD/YYYY hh:mi:ssam') ... and even that will default to being 'am' as their is no explicit value for the am/pm.

lwadwell
0
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
nelslarson,

As Iwadwell told their is no time compoment. their is no need for am/pm since hh will give the time 00 to 24.
if u want  00 to 12hrs then u have to mention hh12:mi:ss instead of hh:mi:ss.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.