Entering dates into a SQL database

Question

The problem I'm having at the moment is that the date is being entered into the SQL database as 30/12/1899. I'm using Access 2003 for the database and can't think of any potential reason why this is happening!

window.status='Loading contingency scripts - please wait...';
audit('Loading contingency scripts');
var conting={ i:0

,start:function(){
    window.status='Loading form - please wait...';
    var t='';
    t+='<form name="frm_conting" id="frm_conting" onsubmit="return false;">';
    t+='<table width="100%" cellspacing="1" cellpadding="0">';
    t+='<tr><td>Date (DD/MM/YY):</td><td><input type="text" size="8" value="'+current_date+'" id="date"></td></tr>';

t+='<tr><td>Time Started:</td><td><select id="timefrom"><option></option>';
  for(h=8;h<23;h++){  
      for(m=0;m<46;m=m+15){  t+='<option value='+nb[h]+':'+nb[m]+'>'+nb[h]+':'+nb[m]+'</option>';  };
     };
t+='</select></td></tr>';

t+='<tr><td>Time Finished:</td><td><select id="timeto"><option></option>';
  for(h=8;h<23;h++){  
  for(m=0;m<46;m=m+15){  t+='<option value='+nb[h]+':'+nb[m]+'>'+nb[h]+':'+nb[m]+'</option>';  };
     };

    t+='</select><tr><td>Extension #:</td><td><input type="text" size="5" value="'+my.extension+'" id="staffid"></td></tr>';
t+='<tr><td>Desk ID:</td><td><input type="text" size="5" value='+my.deskid+' id="desk"></td></tr>';
    t+='<tr><td>Number of calls:</td><td><input type="text" size="5" id="calls"></td></tr>';
    t+='<tr><td>Avid ID:</td><td><input type="text" size="5" id="avid"></td></tr>';
    t+='<tr><td><input type="button" value="Submit" onClick="conting.save()"></td>';    
    t+='</table>';
    t+='</form>';

    div_form.innerHTML=t;       
    window.resizeTo(400,385);
    window.status='';
    }

,save:function(){
var conting_date=frm_conting.date.value; 
 if(!isdate(conting_date)){alert("You have entered an incorrect date.");return false;};

var conting_timefrom=frm_conting.timefrom.value; 
var conting_timeto=frm_conting.timeto.value; 
 if(conting_timefrom==''||conting_timeto==''){alert("You need to enter a starting & finishing time.");return false;}; 
 if(conting_timefrom>conting_timeto){alert("The time you have entered is after the finish time.");return false;}; 

var conting_staffid=frm_conting.staffid.value; 
 if(conting_staffid.length!=5) { alert("You have entered an incorrect extension number.");return false;};

var conting_desk=frm_conting.desk.value; 
 if(conting_desk.length!=5) { alert("You have entered an incorrect desk ID.");return false;}; 

var conting_calls=frm_conting.calls.value; 
 if(isNaN(conting_calls)){alert("You have not entered amount of calls.");return false;};

var conting_avid=frm_conting.avid.value; 
 if(isNaN(conting_avid)){alert("You have entered an incorrect avid ID.");return false;};
 if(conting_avid.length!=5) { alert("You have entered an incorrect avid ID.");return false;}; 

 conn.open(db["contingency"]);
 rs.open("SELECT MAX(prac_id) FROM practice",conn);
var prac_id=rs.fields(0).value+1;
var prac_staffid=frm_conting.staffid.value; 
var prac_date=frm_conting.date.value; 
var prac_timefrom=frm_conting.timefrom.value; 
var prac_timeto=frm_conting.timeto.value; 
var prac_calls=frm_conting.calls.value; 
var prac_avid=frm_conting.avid.value; 
 rs.close();
var q="INSERT INTO practice (prac_id, prac_staffid, prac_date, prac_timefrom, prac_timeto, prac_extension, prac_desk, prac_calls, prac_avid) VALUES ("+prac_id+","+my.id+", "+prac_date+", '"+prac_timefrom+"', '"+prac_timeto+"', '"+my.extension+"', '"+my.deskid+"', '"+prac_calls+"', '"+prac_avid+"')";

 rs.open(q,conn); 
 conn.close();
     alert("Your contingency times were successfully added.");
     window.status='';
   conting.start();
 }

 };
 window.status='';

Answer 1

30/12/1899 is the "zero" date of Access database.

Most likely the prac_date variable does not contain the proper datetime value. Either it is missing, incomplete or malformed. Alert or log it before executing the query to see the actual value going into the query and verify it's correctness.

You might want to take a look at a similar subject discussed in MSDN forum.

Answer 2

What I can think of is - the text entered is empty/not retrieved properly so assimilated to 0 which is the T0 time of either javascript or the database - the text entered is 31/12/99 but this formatting with 2 digits for the year is mapped to 1899 in the database. If that is the case you should explicitly format your date in your insert query.