date format issue in c#

Question

0.00/5 (No votes)

See more:

Hi frens

I have to store date to database (which is of datetime datatype) .......So is thr any way to store in the universal format ???? the issue im facing is , its throwing out of range error in some systems becoz of its regional settings(some have set to UK and some US ) and the format changes accordingly( MMDDYYYY and DDMMYYYY)....so i want to store in such a format so that it s accepted by both...Wht can i do ???? any suggesstions ??

Thanks
DArshan

Posted 26-Mar-13 21:33pm

darshan559

Add a Solution

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 · 2013-03-26T21:41:00

Solution 1

Convert the date to a DateTime before you send it to SQL - SQL (and DateTime) are in a universal format, they store it as a number of microseconds since a specific point in the past. You can then send the date to SQL via a parametrised query:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("INSERT INTO myTable (productName, shipDate) VALUES (@PN, @SD)", con))
        {
        com.Parameters.AddWithValue("@PN", "Sunglasses");
        com.Parameters.AddWithValue("@PD", DateTime.Now);
        com.ExecuteNonQuery();
        }
    }

Posted 26-Mar-13 21:41pm

OriginalGriff

Comments

darshan559 27-Mar-13 4:00am

Hi OriginalGriff Thanks for the reply ...
i used d below code

DateTime dtt1 =Convert.ToDateTime(Calendar1.SelectedDate);
sqlqry = "Insert into tbldateM(date) values ('" + dtt1 + "')";
sqlComm = new SqlCommand(sqlqry, sqlConn);

sqlComm.ExecuteNonQuery();

But i got the error
"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated."

OriginalGriff 27-Mar-13 4:49am

When I said "You can then send the date to SQL via a parametrised query" and gave you the code to do that, why did you ignore it?
*Never* concatenate strings to form an SQL query.
1) In your case, it calls an implicit ToString method on the DateTime object, putting you right back where you were to start with,
and
2) it leave you open to SQL Injection attack which can damage or destroy your database. (Not in this case because a DateTime will never form a valid SQL query, but if you are doing here, you are doing it elswhere, where it can). Always use parametrised queries, if you don't want to lose all your data!

darshan559 27-Mar-13 6:06am

Thank u ..i got it but what if i want to read date from a calender control which is giving me MMDDYYYY and i want to insert into database .... im not able to convert into datetime .... i thot of passing dttl via a parametrised query...

ex: DateTime dtt1 = Convert.ToDateTime("03/27/2013 00:00:00");
its giving "String was not recognized as a valid DateTime"
but if i give DateTime dtt1 = Convert.ToDateTime("27/01/2013 00:00:00");
its working fyn.....
what is the universal way to insert to db ( without considering MMDDYYYY or DDMMYYYY)
please guide me on this

OriginalGriff 27-Mar-13 6:21am

If your calender is always giving you MMDDYYYY then use DateTime.ParseExact:
DateTime dt = DateTime.ParseExact("27/03/2013", "dd/MM/yyyy", CultureInfo.InvariantCulture);
But you do realize that Calender.SelectedDate is a DateTime value already, don't you? You don't need to convert it at all, as it is already in the format you want to send it to SQL via a parametrized query.