INSERT date format in DB


I am trying to insert my date in the database in the dd/mm/yyyy format. The database has been configured to use this particular format. I have even tried inserting the date in the field using SQL explorer directly and a Delphi app, and the format IS dd/mm/yyyy. If I try to insert a number higher than 12 in the dd field ... I get an error, which is fine.
BUT, when I am running the insert statement on the ASP page, for some annoying reason, even though I can see on the response.write that its passing it 12/04/2004, the the database ends up with 04/12/2004!

Can anyone pleeease put me outta my misery?

- The-Jimmy
Who is Participating?
DexstarConnect With a Mentor Commented:
If you want a more flexible way of formatting dates, use this function:

The-JimmyAuthor Commented:
.. oops .. made a typo ... the SQL explorer and Delphi app insert in dd/mm/yyyy ... and the error happens when I put a number higher than 12 in the MM area.

its seems ASP is stuck on the US date format when passing the date to the DB.
Place this in top of you Stored Procedure or the 1st line of you sql statement

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

ALWAYS insert dates in the following format, regardless of the set up of the db

I guarantee this will work with your machine setup.

set dateformat dmy
select month('1/2/04')   /* returns 2 */

set dateformat mdy
select month('1/2/04')   /* returns 1 */
The-JimmyAuthor Commented:
@ Slimshaneey:

I get the following error when I use that format:
String to date conversion error

@ zastil:

I am sending to an Informix server ... so I that dateformat wont work.

It seems ASP is converting the string I am sending it into a date format of some sort according to some configuration I dont know how to set. Thats what would be helpful in this case I believe.

I have even tried the Session.LCID and that dont make any diference.

- The-Jimmy
The-JimmyAuthor Commented:
Here is my SQL:

INSERT INTO cierre_caja(cod_compania,caja_codigo,fecha_cierre,cod_sucursal,cod_docum,usua_abrio,estatus,fecha_abrio) VALUES('1',1,'13/04/2004','001','DP','asandra','A','13/04/2004')
The-JimmyAuthor Commented:
This is my source code:

        sSQL="INSERT INTO cierre_caja(cod_compania,caja_codigo,fecha_cierre,"
        sSQL=sSQL+"cod_sucursal,cod_docum,usua_abrio,estatus,fecha_abrio) VALUES"
        sSQL=sSQL+"('"&trim(Session("MM_cod_compania")) &"',"&(rsCaja.Fields.Item("caja_codigo").Value)
        sSQL=sSQL+",'" & Request.Form("cierre_caja") & "','"& (rsCaja.Fields.Item("cod_sucursal").Value) &"','DP'"
        sSQL=sSQL+",'"&Session("MM_Username")&"','A','" & CStr(date()) & "')"

Try this:
    Function DBDate( dt )
        dt = CDate(dt)
        DBDate = "'" + Month(dt) + "/" + Day(dt) + "/" + Year(dt) + "'"
    End Function

       sSQL="INSERT INTO cierre_caja(cod_compania,caja_codigo,fecha_cierre,"
       sSQL=sSQL+"cod_sucursal,cod_docum,usua_abrio,estatus,fecha_abrio) VALUES"
       sSQL=sSQL+"('"&trim(Session("MM_cod_compania")) &"',"&(rsCaja.Fields.Item("caja_codigo").Value)
       sSQL=sSQL+"," & DBDate(Request.Form("cierre_caja")) & ",'"& (rsCaja.Fields.Item("cod_sucursal").Value) &"','DP'"
       sSQL=sSQL+",'"&Session("MM_Username")&"','A'," & DBDate(date()) & ")"

Hope That Helps,
Have you attempted to use the FormatDateTime() function for ASP?  Also, it might be the fact that you are formatting the date as a string that is changing the formating on you. I have noticed that the CStr function can cause certain values to react in a way that they typically would not. from the looks of the source, you should be able to get away without using it. Try one of the following:

sSQL=sSQL+",'"&Session("MM_Username")&"','A','" & date() & "')"


sSQL=sSQL+",'"&Session("MM_Username")&"','A','" & FormatDateTime(date(), #) & "')"
(in this example, replace # with a numeric value 1-8 to change the formatting around, use a response.write to verify what the formats are and find the one that matches what you need.)

If neither of those work, you could always leave the database value as a string field and have it populate the date in the format you want it to. The big thing here will be to be creative and find a way around the issue. It is possible that you might be limited by the limits of ASP with the way the date field is formatted.

Hope this helps.

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.