OT: How Microsoft Access store datetime value internally?

  • Thread starter Thread starter kids_pro
  • Start date Start date
K

kids_pro

I am getting headache try to figure out how access store datetime value
internally.
I try to build sql string to search for employee records base on date range.

SELECT * FROM employee WHERE BirthDate =#1/7/1992#

When I execute this string from my application records that return show
employee with birthdate 7/1/1992.

What is the best way to work with datetime field?

Thanks in advance,
kids
 
Hello,
Microsoft Access stores Date as Ole Automation date field. According
to MSDN an OLE Automation date is implemented as a floating-point number
whose value is the number of days from midnight, 30 December 1899. For
example, midnight, 31 December 1899 is represented by 1.0; 6 A.M., 1
January 1900 is represented by 2.25; midnight, 29 December 1899
represented by -1.0; and 6 A.M., 29 December 1899 represented by -1.25.

To convert an OLE Automation date to DateTime use DateTime.FromOADate
static method. You can convert a DateTime to Ole Automation date using
ToOADate method. Note that ToOADate method is not static.

HTH. Cheers.
Maqsood Ahmed [MCP,C#]
Kolachi Advanced Technologies
http://www.kolachi.net
 
...
I am getting headache try to figure out how access
store datetime value internally.
I try to build sql string to search for employee records base on date
range.

SELECT * FROM employee WHERE BirthDate =#1/7/1992#

When I execute this string from my application records that return show
employee with birthdate 7/1/1992.

What is the best way to work with datetime field?
Click to expand...

Something along these lines:

string sql =
"SELECT * FROM employee " +
"WHERE BirthDate = @BIRTHDATE";

OleDbCommand cmd = new OleDbCommand ();
cmd.Connection = theConnection;
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;

OleDbParameter par1 = new OleDbParameter ("@BIRTHDATE", OleDbType.Date);
cmd.Parameters.Add(par1);


....and then to execute it.


// Bjorn A
 
...


I forgot to add one important line. (see below)

...


Something along these lines:
Click to expand...

string sql =
"SELECT * FROM employee " +
"WHERE BirthDate = @BIRTHDATE";

OleDbCommand cmd = new OleDbCommand ();
cmd.Connection = theConnection;
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;

OleDbParameter par1 = new OleDbParameter ("@BIRTHDATE", OleDbType.Date);

par1.Value = myBirtDate; // <-- of type DateTime...

cmd.Parameters.Add(par1);



// Bjorn A
 
Back
Top