INSERT date format in DB

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

SET DATEFORMAT dmy

ALWAYS insert dates in the following format, regardless of the set up of the db
yyyy/mm/dd

I guarantee this will work with your machine setup.

Example...

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

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

@ 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

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')

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()) & "')"

@The-Jimmy:

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,
Dex*

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() & "')"

or

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.