[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

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" &");"
0
nelslarson
Asked:
nelslarson
  • 3
  • 3
  • 2
1 Solution
 
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
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
nelslarsonAuthor 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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now