Solved

INSERT date format in DB

Posted on 2004-04-13
11
656 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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