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, May 13 '15 at 14:24

score 2 · Accepted Answer · edited May 23 '17 at 11:46

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)

score 1 · Answer 2 · edited May 13 '15 at 14:25

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 '15 at 14:25

Soner Gönül

97,193
102
206
364

answered May 13 '15 at 14:23

JeffFerguson

2,952
19
28

score 0 · Answer 3 · answered May 13 '15 at 14:24

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 '15 at 14:24

L-Four

13,345
9
65
109

getting this Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression, I'm trying to figure why – Yan May 13 '15 at 14:31
Just a guess: can you try: clickdate.Value = dtclickdate.ToString(); – L-Four May 13 '15 at 14:33
@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)` – Heinzi May 13 '15 at 14:34
1

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; – Yan May 13 '15 at 14:48

insert date with time in access database c#

3 Answers3