ASP & MYSQL Date formatting problems

Posted on 2006-06-05
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, 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.property_info, arm.investors
WHERE Pro_ID = MMColParam AND = arm.property_info.Property_ID AND = arm.investors.Investor_ID

Any help is appreciated.  Thanks.
Question by:blob150
    LVL 6

    Accepted Solution

    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


    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 = ""
      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, 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.property_info, arm.investors  WHERE Pro_ID = " + Replace(Rs_Update__MMColParam, "'", "''") + " AND = arm.property_info.Property_ID AND = arm.investors.Investor_ID"
    Rs_Update.CursorType = 0
    Rs_Update.CursorLocation = 2
    Rs_Update.LockType = 1

    Rs_Update_numRows = 0
    <!--#INCLUDE file="../" -->
    <!--#INCLUDE file="../dbFuncs.asp" -->
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
    <html xmlns="">
    <title>Alliance Resource Management, LLC</title>
    <!-- STYLESHEET -->
    <link href="../css/main.css" rel="stylesheet" type="text/css">
    <link href="../css/admincss.css" rel="stylesheet" type="text/css">
    <!-- JAVASCRIPT -->
    <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
      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
    LVL 1

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    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…
    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now