insert date with time in access database c#

Question

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);

Label3.Text = DateTime.Now.ToString(); that give me the date + time, but for some reason in access doesn't work, maybe I have to to something in the access database In formart in access I have "General Date" — Yan
– Yan, Commented May 13, 2015 at 14:24

Community · Accepted Answer · 2017-05-23 11:46:21Z

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)

Soner Gönül · Accepted Answer · 2015-05-13 14:25:06Z

1

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.

edited May 13, 2015 at 14:25

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

answered May 13, 2015 at 14:23

JeffFerguson

3,00220 silver badges28 bronze badges

Comments

L-Four · Accepted Answer · 2015-05-13 14:24:44Z

0

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.

answered May 13, 2015 at 14:24

L-Four

13.6k9 gold badges73 silver badges116 bronze badges

4 Comments

Yan Over a year ago

getting this Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression, I'm trying to figure why

L-Four Over a year ago

Just a guess: can you try: clickdate.Value = dtclickdate.ToString();

Heinzi Over a year ago

@Yan: That's because Access does not like Milliseconds. Remove them first: dtm = New DateTime(dtm.Year, dtm.Month, dtm.Day, dtm.Hour, dtm.Minute, dtm.Second)

Yan Over a year ago

you are genius =D @Heinzi DateTime dtclickdate; DateTime dtm=DateTime.Now; dtm = new DateTime(dtm.Year, dtm.Month, dtm.Day, dtm.Hour, dtm.Minute, dtm.Second); dtclickdate = dtm;

Collectives™ on Stack Overflow

insert date with time in access database c#

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related