How to insert date time data to sql database?

Question

0.00/5 (No votes)

See more:

I want to insert a data of dd:mm:yy h:m:s:m types of data in sql database in c# ado.net. I am getting an error

C#

DateTime drdt = new DateTime();
drdt =Convert.ToDateTime(drd.ItemArray.GetValue(2));
string dttime=drdt.ToString();
SqlCommand c_m_d = new SqlCommand("insert into ORI_DATA(Date_Time) values('"+drdt+"')", c_o_n);
c_m_d.ExecuteScalar();

The actual data is get inserted in mm:dd:yy h:m:s:m format then how to insert data in dd:mm:yy h:m:s:m format?

Posted 5-Apr-11 0:13am

maheshpardeshi

Updated 5-Apr-11 0:15am

Sandeep Mewara

v2

Add a Solution

6 solutions

Solution 1

The format of date does not matter in the database. You should simply save it in the database. All the formatting should be done in the UI using IFormatProvider.

Posted 5-Apr-11 0:16am

dan!sh

Solution 2

The actual data is get inserted in mm:dd:yy h:m:s:m format then how to insert data in dd:mm:yy h:m:s:m format?
It's just the format difference. You have the correct date stored. SQL is storing it in the default format specified for the locale that is being used.

You don't need to worry about it. When you get back the data, you will get the correct value. Further, you can transform the same into desired format.

In case you want to see the data in the format you want to:
1. Change the language settings and the date format as per expected
OR
2. Use a string field instead of date field and store the way you want.

I would not recommend 2 at all. Further, would still say, data is fine so no change is needed.

Posted 5-Apr-11 0:19am

Sandeep Mewara

Solution 5

Please do not do any formating to datetime before you enter this in db. Once you retrieve this, only then you should apply appropriate formating to show it in the way you want.

Hope this helps

Posted 5-Apr-11 1:24am

Sunil Pandita

Solution 3

try this link

Link [^]

Posted 5-Apr-11 0:20am

Mahendra.p25

Comments

Kim Togo 5-Apr-11 7:29am

Storing DateTime and showing DateTime class is not the same. To show a DateTime in UI, it is best to use the System default on showing date and time informations

Solution 6

A very simple way is to call dt.ToString("s") - Sortable date/time pattern.

SqlCommand c_m_d = new SqlCommand("insert into ORI_DATA(Date_Time) values('"+drdt.ToString("s")+"')", c_o_n)

The Sortable ("s") Format Specifier[^]

Posted 5-Apr-11 1:34am

Kim Togo

Comments

Johnny J. 5-Apr-11 8:48am

Not a very good idea if you're working with a localizable app, you can easily get errors doing it this way. Better to add it to the SqlCommand as an SqlParameter...

Kim Togo 5-Apr-11 16:10pm

The Sortable date/time pattern never changes it formating, no matter what culture the program runs under. But using SqlParameter is the best way.

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 · Accepted Answer · 2011-04-05T00:22:00

Rule 1: Never convert data if you don't have to.

If you already have the data as a DateTime, leave it as a DateTime. Make sure that the field in the database is Date or DateTime and insert the data like this:

DateTime drdt = new DateTime();
drdt = Convert.ToDateTime(drd.ItemArray.GetValue(2)); // No idea why you do this, but Hey! It's your code!
SqlCommand c_m_d = new SqlCommand("INSERT INTO ORI_DATA (Date_Time) values(@DT)", c_o_n);
c_m_d.Parameters.AddWithValue("@DT", drdt);
c_m_d.ExecuteNonQuery();

That way you can use it in the database sensibly: For example by sorting by date, and so forth.

Only convert data when you need a specific format for a specific reason: convert it to a string when you are going to print it, for example. Even then, convert it as late as you can.