Solved

INSERT date format in DB

Posted on 2004-04-13
11
636 Views
Last Modified: 2012-08-13
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
Comment
Question by:The-Jimmy
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 2

Author Comment

by:The-Jimmy
ID: 10815168
.. 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
 
LVL 3

Expert Comment

by:zastil
ID: 10815233
Place this in top of you Stored Procedure or the 1st line of you sql statement

SET DATEFORMAT dmy
0
 
LVL 11

Expert Comment

by:Slimshaneey
ID: 10815284
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
 
LVL 3

Expert Comment

by:zastil
ID: 10815301
Example...

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

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

Author Comment

by:The-Jimmy
ID: 10815925
@ 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:The-Jimmy
ID: 10816486
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
 
LVL 2

Author Comment

by:The-Jimmy
ID: 10816587
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
 
LVL 19

Expert Comment

by:Dexstar
ID: 10816654
@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
 
LVL 8

Expert Comment

by:RevelationCS
ID: 10816696
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
 
LVL 19

Accepted Solution

by:
Dexstar earned 250 total points
ID: 10816710
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now