ASP & MYSQL Date formatting problems

Posted on 2006-06-05
Medium Priority
Last Modified: 2008-02-01
I am having date formatting problems with ASP and MYSQL.  The date is stored as yyyy-mm-dd in MYSQL but displays as mm/dd/yy when I pull a recordset in Dreamweaver.  This is causing a problem when I try to setup and update page because the current record displays in mm/dd/yy format and when the update is submitted MYSQL does not read it properly.

Can someone tell me how to either store the dates in MYSQL as mm/dd/yy, or display them as yyyy-mm-dd so they are consistent?  I tried to use the Date_Format function in the Select statement and that worked great when I run the query directly, but when I post the page I get and invalid character error for "%".  Here is the SQL statement.  If I could get thios to work - problem solved.

SELECT arm.deals.Investor_ID, Pro_ID, Pro_AdminStat, Property_Address1, Property_Address2, Property_Address3, Property_City, Property_State, Property_Zip, DATE_FORMAT(Ext_Date, "%Y-%m-%d") as Ext_Date, Add_Bonus, Settled_Date, Notes, Total_Amt, Pro_DescriptFooter, arm.investors.Investor_ID, Inv_email, Inv_Contact, Pro_DateMod
FROM arm.deals, arm.property_info, arm.investors
WHERE Pro_ID = MMColParam AND arm.deals.Property_ID = arm.property_info.Property_ID AND arm.deals.Investor_ID = arm.investors.Investor_ID

Any help is appreciated.  Thanks.
Question by:blob150

Accepted Solution

inviser earned 375 total points
ID: 16834932
ASP and MySQL obviously differ with their date formats. ADO however, which is what ASP uses to interface with databases, automatically accounts for dates extracted from a MySQL database by converting them from yyyy-mm-dd to mm/dd/yy. As you noticed, it does not convert back to MySQL format when you update or insert a record.

The best way to deal with this is create a function that converts mm/dd/yy to yyyy-mm-dd and use it when you do update or insert queries. Here is the function I just described. Use the top function. The 2nd is a utility function and the last line of code is an example of how to use it.

function DateToMySQL(strDate)
    dim tmp
    tmp = split(strDate, "/")
    tmp(0) = formatNumber(tmp(0))
    tmp(1) = formatNumber(tmp(1))

    DateToMySQL = "20" & tmp(2) & "-" & tmp(0) & "-" & tmp(1)
end function

function formatNumber(num)
    if int(num) < 10 then
        formatNumber = "0" & num
        formatNumber = num
    end if
end function

response.write DateToMySQL("4/2/06")

Author Comment

ID: 16835128

Thank you for the quick response.  Clearly I am an amateur at this stuff, and where I can follow the logi in what you posted, I am not sure how or where to integrate it on my page.  If you can point me in the right direction there I should be in good shape and I'll boost the point total a little.  The variable in questions is Ext_Date.  Here is the code for the page:

<%Option Explicit%>
<!--#include file="../Connections/Deals.asp" -->
' *** Edit Operations: declare variables

Dim MM_editAction
Dim MM_abortEdit
Dim MM_editQuery
Dim MM_editCmd

Dim MM_editConnection
Dim MM_editTable
Dim MM_editRedirectUrl
Dim MM_editColumn
Dim MM_recordId

Dim MM_fieldsStr
Dim MM_columnsStr
Dim MM_fields
Dim MM_columns
Dim MM_typeArray
Dim MM_formVal
Dim MM_delim
Dim MM_altVal
Dim MM_emptyVal
Dim MM_i

MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
  MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
MM_abortEdit = false

' query string to execute
MM_editQuery = ""
' *** Update Record: set variables

If (CStr(Request("MM_update")) = "form1" And CStr(Request("MM_recordId")) <> "") Then

  MM_editConnection = MM_Deals_STRING
  MM_editTable = "arm.deals"
  MM_editColumn = "Pro_ID"
  MM_recordId = "" + Request.Form("MM_recordId") + ""
  MM_editRedirectUrl = "email_deal_change.asp"
  MM_fieldsStr  = "ProAdminStat|value|ext_date|value|Add_Bonus|value|Settled_Date|value|Total_Amount|value|Notes|value|dat_mod|value"
  MM_columnsStr = "Pro_AdminStat|',none,''|Ext_Date|',none,NULL|Add_Bonus|none,none,NULL|Settled_Date|',none,''|Total_Amt|none,none,NULL|Notes|',none,''|Pro_DateMod|',none,''"

  ' create the MM_fields and MM_columns arrays
  MM_fields = Split(MM_fieldsStr, "|")
  MM_columns = Split(MM_columnsStr, "|")
  ' set the form values
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_fields(MM_i+1) = CStr(Request.Form(MM_fields(MM_i)))

  ' append the query string to the redirect URL
  If (MM_editRedirectUrl <> "" And Request.Form <> "") Then
    If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0 And Request.Form <> "") Then
      MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.Form
      MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.Form
    End If
  End If

End If
' *** Update Record: construct a sql update statement and execute it

If (CStr(Request("MM_update")) <> "" And CStr(Request("MM_recordId")) <> "") Then

  ' create the sql update statement
  MM_editQuery = "update " & MM_editTable & " set "
  For MM_i = LBound(MM_fields) To UBound(MM_fields) Step 2
    MM_formVal = MM_fields(MM_i+1)
    MM_typeArray = Split(MM_columns(MM_i+1),",")
    MM_delim = MM_typeArray(0)
    If (MM_delim = "none") Then MM_delim = ""
    MM_altVal = MM_typeArray(1)
    If (MM_altVal = "none") Then MM_altVal = ""
    MM_emptyVal = MM_typeArray(2)
    If (MM_emptyVal = "none") Then MM_emptyVal = ""
    If (MM_formVal = "") Then
      MM_formVal = MM_emptyVal
      If (MM_altVal <> "") Then
        MM_formVal = MM_altVal
      ElseIf (MM_delim = "'") Then  ' escape quotes
        MM_formVal = "'" & Replace(MM_formVal,"'","''") & "'"
        MM_formVal = MM_delim + MM_formVal + MM_delim
      End If
    End If
    If (MM_i <> LBound(MM_fields)) Then
      MM_editQuery = MM_editQuery & ","
    End If
    MM_editQuery = MM_editQuery & MM_columns(MM_i) & " = " & MM_formVal
  MM_editQuery = MM_editQuery & " where " & MM_editColumn & " = " & MM_recordId

  If (Not MM_abortEdit) Then
    ' execute the update
    Set MM_editCmd = Server.CreateObject("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_editConnection
    MM_editCmd.CommandText = MM_editQuery

    If (MM_editRedirectUrl <> "") Then
    End If
  End If

End If
Dim Rs_Update__MMColParam
Rs_Update__MMColParam = "310"
If (Request.QueryString("Pro_ID")  <> "") Then
  Rs_Update__MMColParam = Request.QueryString("Pro_ID")
End If
Dim Rs_Update
Dim Rs_Update_numRows

Set Rs_Update = Server.CreateObject("ADODB.Recordset")
Rs_Update.ActiveConnection = MM_Deals_STRING
Rs_Update.Source = "SELECT arm.deals.Investor_ID, Pro_ID, Pro_AdminStat, Property_Address1, Property_Address2, Property_Address3, Property_City, Property_State, Property_Zip, Ext_Date, Add_Bonus, Settled_Date, Notes, Total_Amt, Pro_DescriptFooter, arm.investors.Investor_ID, Inv_email, Inv_Contact, Pro_DateMod  FROM arm.deals, arm.property_info, arm.investors  WHERE Pro_ID = " + Replace(Rs_Update__MMColParam, "'", "''") + " AND arm.deals.Property_ID = arm.property_info.Property_ID AND arm.deals.Investor_ID = arm.investors.Investor_ID"
Rs_Update.CursorType = 0
Rs_Update.CursorLocation = 2
Rs_Update.LockType = 1

Rs_Update_numRows = 0
<!--#INCLUDE file="../adovbs.inc" -->
<!--#INCLUDE file="../dbFuncs.asp" -->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<title>Alliance Resource Management, LLC</title>
<link href="../css/main.css" rel="stylesheet" type="text/css">
<link href="../css/admincss.css" rel="stylesheet" type="text/css">
<script src="../js/arm.js" type="text/javascript"></script>
<!-- META TAGS -->
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta http-equiv="Pragma" content="no-cache">
<meta http-equiv="Expires" content="-1">
<meta name="keywords" content="Real Estate, Rent, Rental, Lease Purchase, Investor, Alliance Resource Management, Atlanta, Gwinnett, Dekalb">
<meta name="description" content="Alliance Resource Management, LLC">
<meta name="author" content="Alliance Resource Management, LLC">
<meta name="copyright" content="2005">
<script type="text/JavaScript">
function YY_checkform() { //v4.71
//copyright (c)1998,2002 Yaromat.com
  var a=YY_checkform.arguments,oo=true,v='',s='',err=false,r,o,at,o1,t,i,j,ma,rx,cd,cm,cy,dte,at;
  for (i=1; i<a.length;i=i+4){
    if (a[i+1].charAt(0)=='#'){r=true; a[i+1]=a[i+1].substring(1);}else{r=false}
    if (o.type=='text'||o.type=='password'||o.type=='hidden'){
      if (r&&v.length==0){err=true}
      if (v.length>0)
      if (t==1){ //fromto
        ma=a[i+1].split('_');if(isNaN(v)||v<ma[0]/1||v > ma[1]/1){err=true}
      } else if (t==2){
        rx=new RegExp("^[\\w\.=-]+@[\\w\\.-]+\\.[a-zA-Z]{2,4}$");if(!rx.test(v))err=true;
      } else if (t==3){ // date
          dte=new Date(cy,cm,cd);
      } else if (t==4){ // time
      } else if (t==5){ // check this 2
      } else if (t==6){ // the same
    } else
    if (!o.type&&o.length>0&&o[0].type=='radio'){
          at = a[i].match(/(.*)\[(\d+)\].*/i);
      if (t==1&&o2&&o2.checked&&o1&&o1.value.length/1==0){err=true}
      if (t==2){
        if(!oo){s+='* '+a[i+3]+'\n'}
    } else if (o.type=='checkbox'){
    } else if (o.type=='select-one'||o.type=='select-multiple'){
    }else if (o.type=='textarea'){
    if (err){s+='* '+a[i+3]+'\n'; err=false}
  if (s!=''){alert('The required information is incomplete or contains errors:\t\t\t\t\t\n\n'+s)}
  document.MM_returnValue = (s=='');
<body bgcolor="#004D91">
<a href="../index.htm"><img src="../images/ARM_logo_mini.gif" border="0" /></a>
<div id="back">Main Admin page / <a href="../index.htm">Home</a> / <a href="../logout.asp">Logout</a><br />
<div id="header"> Update  Deal For Investor #: <span class="title_data"><%=(Rs_Update.Fields.Item("Investor_ID").Value)%></span></div>
<table width="100%" border="0" cellspacing="0" cellpadding="3">
    <td width="50%"><form ACTION="<%=MM_editAction%>" METHOD="POST" name="form1" id="form1" onsubmit="YY_checkform('form1','ext_date','^\([0-9]{4}\)\\-\([0-9][0-9]\)\\-\([0-9][0-9]\)$#3#2#1','3','Field \'ext_date\' is not valid.');return document.MM_returnValue">
      <table width="100%" border="0" cellspacing="0" cellpadding="3">
          <td class="adminrowlabel">Projetced Settlement Date: </td>
          <td class="adminrowlabel">Status:</td>
          <td><select name="ProAdminStat" id="ProAdminStat">
            <option value="Active" <%If (Not isNull((Rs_Update.Fields.Item("Pro_AdminStat").Value))) Then If ("Active" = CStr((Rs_Update.Fields.Item("Pro_AdminStat").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%>>Active</option>
            <option value="Settled" <%If (Not isNull((Rs_Update.Fields.Item("Pro_AdminStat").Value))) Then If ("Settled" = CStr((Rs_Update.Fields.Item("Pro_AdminStat").Value))) Then Response.Write("selected=""selected""") : Response.Write("")%>>Settled</option>
          <td width="22%" class="adminrowlabel">Extended Settlement Date: </td>
          <td width="28%"><input name="ext_date" type="text" id="ext_date" value="<%=(Rs_Update.Fields.Item("Ext_Date").Value)%>" /></td>
          <td class="adminrowlabel">Additional Bonus: </td>
          <td><input name="Add_Bonus" type="text" id="Add_Bonus" value="<%=(Rs_Update.Fields.Item("Add_Bonus").Value)%>" /></td>
          <td class="adminrowlabel">Settled Date: </td>
          <td><input name="Settled_Date" type="text" id="Settled_Date" value="<%=(Rs_Update.Fields.Item("Settled_Date").Value)%>" /></td>
          <td class="adminrowlabel">Total Amount: </td>
          <td><input name="Total_Amount" type="text" id="Total_Amount" value="<%=(Rs_Update.Fields.Item("Total_Amt").Value)%>" /></td>
          <td valign="top" class="adminrowlabel">Notes:</td>
          <td><textarea name="Notes" id="Notes"><%=(Rs_Update.Fields.Item("Notes").Value)%></textarea></td>
          <td valign="top" class="adminrowlabel">Date Last Modified:<br />
            <span class="tiny2">(This will be updated with today's date) </span></td>
          <td height="41">&nbsp;</td>
          <td><input type="submit" name="Submit" value="Update Deal" />
            <input name="email" type="hidden" id="email" value="<%=(Rs_Update.Fields.Item("Inv_email").Value)%>" />
            <input name="name" type="hidden" id="name" value="<%=(Rs_Update.Fields.Item("Inv_Contact").Value)%>" />
            <input name="address1" type="hidden" id="address1" value="<%=(Rs_Update.Fields.Item("Property_Address1").Value)%>" />
            <input name="address2" type="hidden" id="address2" value="<%=(Rs_Update.Fields.Item("Property_Address2").Value)%>" />
            <input name="address3" type="hidden" id="address3" value="<%=(Rs_Update.Fields.Item("Property_Address3").Value)%>" />
            <input name="number" type="hidden" id="number" value="<%=(Rs_Update.Fields.Item("Investor_ID").Value)%>" />
            <input name="dat_mod" type="hidden" id="dat_mod" value="<%=Now()%>" /></td>
            <input type="hidden" name="MM_update" value="form1">
      <input type="hidden" name="MM_recordId" value="<%= Rs_Update.Fields.Item("Pro_ID").Value %>">
</form>    </td>
    <td width="50%" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="3">
        <td width="34%" valign="top" class="adminrowlabel">Property Address: </td>
        <td width="66%"><%=(Rs_Update.Fields.Item("Property_Address1").Value)%><br />
          <%=(Rs_Update.Fields.Item("Property_Address2").Value)%><br />
        <td valign="top" class="adminrowlabel">City, State Zip: </td>
        <td><%=(Rs_Update.Fields.Item("Property_City").Value)%>, <%=(Rs_Update.Fields.Item("Property_State").Value)%>&nbsp;<%=(Rs_Update.Fields.Item("Property_Zip").Value)%></td>

Set Rs_Update = Nothing

Expert Comment

ID: 20052456
And I putted Session.LCID = 1053 (for sweden) in top of the script, then it's formatted the dates correct.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

807 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