• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • Last Modified:

INSERT date format in DB

Hi!

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?
Thanks,

- The-Jimmy
0
The-Jimmy
Asked:
The-Jimmy
  • 4
  • 2
  • 2
  • +2
1 Solution
 
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.
0
 
zastilCommented:
Place this in top of you Stored Procedure or the 1st line of you sql statement

SET DATEFORMAT dmy
0
 
SlimshaneeyCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
zastilCommented:
Example...

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

set dateformat mdy
select month('1/2/04')   /* returns 1 */
0
 
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
0
 
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')
0
 
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()) & "')"
0
 
DexstarCommented:
@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*
0
 
RevelationCSCommented:
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.

0
 
DexstarCommented:
If you want a more flexible way of formatting dates, use this function:
http://www.aspfree.com/c/a/ASP-Code/Format-DateTime-Function--fmtDateTime-by-Kevin-Turner/

D*
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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