Oracle INSERT Statement with TO_DATE Issue

[The_Grudge]

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:

 "'," & "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?

[GaryMazzone]

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.

[The_Grudge]

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?

[GaryMazzone]

Remember M stands for Month.

[GaryMazzone]

Will this work?

TO_DATE(TO_CHAR(dtDateTime_Stamp,'MM/DD/YYYY HH24:MI:SS'),'MM/DD/YYYY HH24:MI:SS')