How to save datetime into access database

Question

1.00/5 (1 vote)

See more:

how to save datetime into access with format "dd/MM/yyyy hh:mm:ss"?
i'm try using this format but miss with day and month ..
in my access db using format " dd/mm/yyyy hh:nn:ss "
so i use format like this and haven't miss ..

What I have tried:

dim actdate = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss")

and using save method :

cmd.parameters.Add(New OleDbParameter("letdate", CType(actdate, Date)))

Posted 7-Sep-17 18:28pm

Khabibb Mubarakk

Updated 7-Sep-17 20:06pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 8-Sep-17 0:41am

Do not use string for dates. Dates do not have a format until you display them.

Khabibb Mubarakk 8-Sep-17 0:57am

just like this?

 actdate = DateTime.Now

PIEBALDconsult 8-Sep-17 8:24am

Yes, better.

Khabibb Mubarakk 8-Sep-17 10:17am

mismatch bro

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-09-07T20:06:00

Solution 1

Like this:

VB

Dim actDate as DateTime = DateTime.Now
cmd.parameters.AddWithValue("letdate", sctDate)

Posted 7-Sep-17 20:06pm

OriginalGriff

Comments

Khabibb Mubarakk 8-Sep-17 6:21am

it's make mismacth criteria !

OriginalGriff 8-Sep-17 6:46am

Then you are trying to store dates as strings, which is a very bad idea - it always comes back to bite you later.
Change your DB so you store items in appropriate datatypes: integers, in INT, floating point in FLOAT, money in DECIMAL, and Dates in DATE, DATETIME, or DATETIME2 columns. Only string based data belongs in VARCHAR or NVARCHAR columns.

Khabibb Mubarakk 8-Sep-17 7:43am

i won't to store date as string..
exactly i set db in appropriate datatype.
but when i try save using date.now or datetime.now then i got message mismatch criteria..

OriginalGriff 8-Sep-17 8:18am

Show the exact code you are using, and your table design.

Khabibb Mubarakk 8-Sep-17 10:29am

this exact my code as you want
letdate on access database using datetime datatype..

call koneksi()
sql = "insert into cube([no],[letdate])" & " "values(?,?)"
cmd = New OleDbCommand(sql, conn)
dim actdate as datetime = DateTime.Now
With cmd.Parameters
    .Add(New OleDbParameter("@no", CType(serno.Text, Integer)))        
    .Add(New OleDbParameter("@letdate", CType(actdate, Date)))
End With
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()

OriginalGriff 8-Sep-17 10:32am

Is there any point in my telling you what to do if you ignore it and then complain it doesn't work?

Khabibb Mubarakk 8-Sep-17 10:39am

which one ?
this one

Dim actDate as DateTime = DateTime.Now
cmd.parameters.AddWithValue("letdate", sctDate)

before comment mismatch i have tried first
then i say this is give me mismatch message

OriginalGriff 8-Sep-17 10:47am

Should be "actDate" not "sctDate" - finger trouble at my end.

Khabibb Mubarakk 8-Sep-17 10:58am

i know that u want exactly "actdate" ..
so i trying using "acdate"
whatever.. i still confuse why can't save datetime.now..
I'm using Hide Copy Code

  dim actdate as datetime = datetime.now.tostring("MM/dd/yyyy HH:mm:ss")

this will be saving correct date on access ..
i think maybe i must follow general format on access.

OriginalGriff 8-Sep-17 11:05am

No, it won't.

It's a string. Don't send it as a string; Don't store it as a string.
Send it as a DateTime, as in my example; store it as a DATE, DATETIME, or DATETIME2 column.

Never convert things unnecessarily...

Khabibb Mubarakk 8-Sep-17 11:18am

but how? if i find correct method to store using datetime.now i will be use it..

i send as string
so for what this declared " dim actdate as datetime " ?
then in my db still using datetime datatype..
can make conflic in sometime?
i think all its look oke..

OriginalGriff 8-Sep-17 11:29am

Let's go right back to the beginning...
Use Access to generate a script for your table definition, and post that.

Khabibb Mubarakk 8-Sep-17 11:40am

how to Use Access to generate a script for your table definition ?

OriginalGriff 8-Sep-17 12:03pm

Sorry, I forgot how primitive Access is ... I use SSMS.

Go here:
https://www.pcworld.com/article/168319/MDB_Viewer_Plus.html
And download the EXE - it's safe and very handy.
Run the app, and open your MDB file.
Use the "Table" menu, and select "Generate SQL - CREATE".
That'll open a window with the CREATE TABLE command to generate your table.

Khabibb Mubarakk 8-Sep-17 12:28pm

yeah letdate using datetime .and what ?

OriginalGriff 8-Sep-17 12:33pm

Show the CREATE TABLE command it generated.

Khabibb Mubarakk 8-Sep-17 12:52pm

CREATE TABLE [CUBE] 
(   [NO] SMALLINT,
   [LETDATE] DATETIME 

)

OriginalGriff 8-Sep-17 14:03pm

OK - so the code I showed should work fine - it passes a DATETIME value directly and your table is a DATETIME column, so that should work. So ... logically, it's not LETDATE that causes the problem. Look at NO - it's a SMALLINT, so teh largest number it can hold is 32767, max max positive value in a 16 bit number. But Integer in .NET is a 32 bit number - which can hold a lot bigger values. What value does the debugger say is in serno.Text when you pass that? Don't guess or assume - use the debugger and look!

Khabibb Mubarakk 11-Sep-17 15:36pm

I think miss format date.. i try just store date.now into letdate .i still get msg mismatch

PIEBALDconsult 8-Sep-17 8:25am

Do NOT store dates as strings. Ever.