Oracle INSERT Statement with TO_DATE Issue
I've done this countless times in VB6 but for some reason I'm having an issue in .NET.
In my code I have an Oracle command that contains an INSERT statement. Here's a snippet from my SQL:
Code:
"'," & "To_Date('" & Format$(dtDateTime_Stamp, "MM/dd/yyyy hh:mm") & _
The value of strDateTime_Stamp is #1/19/2011 9:42:22 AM#
I'm getting an error ( "ORA-01830: date format picture ends before converting entire input string") because I don't know how to correctly assign the AM/PM tag on the end -- you'll notice it just says "MM/dd/yyyy hh:mm" in my code. I've tried a bunch of different things but nothing works. I also find it odd that if I change the "MM/dd/yyyy hh:mm" to "mm/dd/yyyy hh:mm" (little m for month) it gives me a 42 for the month value, not a 1. The capital M's fix that but I'm curious to know why.
And yah, how to I get it to show the AMPM on the end?
Re: Oracle INSERT Statement with TO_DATE Issue
Because m stands for minutes and M stands for the month. The reason the error is coming up is your format end at hh:mm notice that the value passed has seconds and AM in it.
Re: Oracle INSERT Statement with TO_DATE Issue
Thanks.
I put MM/dd/yyyy hh:mm:ss AM but it now returns '01/19/2011 09:42:22 A1'
Where's the 1 coming from?
Re: Oracle INSERT Statement with TO_DATE Issue
Remember M stands for Month.
Re: Oracle INSERT Statement with TO_DATE Issue
Will this work?
TO_DATE(TO_CHAR(dtDateTime_Stamp,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')