CODEWITHSUNDEEP
c#asp.netoledb
Yan
Yan

Reputation: 25

insert date with time in access database c#

In the access I'm only getting the date 5/12/2015 but no the time, I need something like this

5/12/2015 4:56 PM saved in the access database

  DateTime dtclickdate = DateTime.Now;
  OleDbParameter clickdate = new OleDbParameter("@clickdate ", OleDbType.DBDate);
  clickdate.Value = dtclickdate; 
  cmd.Parameters.Add(clickdate);

Upvotes: 1

Views: 1898

Answers (3)

Heinzi
Heinzi

Reputation: 172448

Have a look at the MSDN description of OleDbType:

DBDate: Date data in the format yyyymmdd (DBTYPE_DBDATE). This maps to DateTime.

As you can see, DBDate does not contain a time component. I would suggest to use Date instead:

Date: Date data, stored as a double (DBTYPE_DATE). The whole portion is the number of days since December 30, 1899, and the fractional portion is a fraction of a day. This maps to DateTime.

According to the following Microsoft Knowledge Base article, this is the correct type to use for Access Date/Time fields.

Quoted from INFO: OleDbType Enumeration vs. Microsoft Access Data Types:

Access Type Name  Database Data Type  OLE DB Type  .NET Framework Type  Member Name
    ...
Date/Time         DateTime            DBTYPE_DATE  System.DateTime    OleDbType.Date
    ...

StackOverflow should really add support for tables...

PS: Note that OLEDB does not like Milliseconds in Date/Time fields. If you get a Data type mismatch in criteria expression error, remove the milliseconds:

dtm = New DateTime(dtm.Year, dtm.Month, dtm.Day, dtm.Hour, dtm.Minute, dtm.Second)

Upvotes: 2

JeffFerguson
JeffFerguson

Reputation: 3002

You should use the OleDbType.DBTimeStamp enumeration, which maps to DateTime:

DateTime dtclickdate = DateTime.Now;
OleDbParameter clickdate = new OleDbParameter("@clickdate ", OleDbType.DBTimeStamp);
clickdate.Value = dtclickdate; cmd.Parameters.Add(clickdate);

See this documentation for more information.

Upvotes: 1

L-Four
L-Four

Reputation: 13551

Try OleDbType.DBTimeStamp:

DateTime dtclickdate = DateTime.Now;
OleDbParameter clickdate = new OleDbParameter("@clickdate ", OleDbType.DBTimeStamp);
clickdate.Value = dtclickdate; 
cmd.Parameters.Add(clickdate);

Reason: this also stores time fraction as explained here.

Upvotes: 0

Related Questions