VB.NET - Insert datetime value into a SQL database in the current systems datetime format

Asked By Hrushikesh Pande on 18-May-10 03:42 AM
Hi,
    I have a DateTimePicker control on the form. My system date format is set as (yy/MM/dd) in shortdate format. I need to insert the selected date from the DateTimePicker control into the SQL database table column of type datetime. But in this case when an insert query is fired, it stores the date in some other format whereas I want to store it in the current systems datetime format itself i.e  (yy/MM/dd).

Same when done for other formats(M/d/yyyy, M/d/yy, MM/dd/yy, MM/dd/yyyy) where the date format doesn't start with "yy" seems to work fine and stores date in proper system datetime format.

Any suggestions?

Thanks in advance.
Sagar P replied to Hrushikesh Pande on 18-May-10 04:00 AM
In sql server we have default datetime format as MM/dd/yyyy.
So when you insert datetime value it converts in date format of sql server and then inserts in a table.

So if you want to insert in a format you can change column datatype from datetime to nvarchar. So that it will insert the way you insert as a string. You can also convert datetime in format you want in sql server using Convert....
check this link for same;
http://www.sqlusa.com/bestpractices2005/centurydateformat/
Sasha Kotlo replied to Hrushikesh Pande on 18-May-10 04:22 AM
If you're using parametrized queries then the Date format of MSSQL won't be of any problem. When passing a parameter to SqlCommand object just pass the DateTime object as a value of the parameter and it will convert just fine. To convert your textbox value to DateTime object use the following code:

DateTime dateTimeValue = DateTime.ParseExact(txtStartDate.Text, "yy/MM/dd", CultureInfo.InvariantCulture);

And Sujit there's no need to convert a column to nvarchar, please stop misinforming people with comments like that.

Best regards.
Hrushikesh Pande replied to Sagar P on 18-May-10 05:02 AM
Hi Sujit,
           Thanks for your reply. Well its correct that the default format in which SQL stores dates is MM/dd/yyyy, but that aint true when the system datetime format has been set to yy/MM/dd, its actually storing the date in a dd/yy/MM format. I tried it out as mentioned in the code below,the column type in DB still being of the datetime type:

Dim


dt As DateTime = DateTimePicker1.Value




Dim com As New SqlCommand("insert into SystemDateFormat values(Convert(varchar, '" + dt + "', 11))",con)

com.ExecuteNonQuery()

In this case it inserts the date into the DB in dd/yy/MM format.

Any suggestions?

Thanks.

Hrushikesh Pande replied to Sasha Kotlo on 18-May-10 05:18 AM
Hi Sasha,
             Thanks for your reply. I tried out the same but that didn't workout either. Code is as mentioned below:

Dim


dt As DateTime =
DateTime.ParseExact(DateTimePicker1.Value.ToShortDateString,
"yy/MM/dd",CultureInfo.InvariantCulture)

Dim


com As New SqlCommand("insert into SystemDateFormat values('" + dt + "')", con)

com.ExecuteNonQuery()

Now in this case, the variable dt holds a value #5/18/2010# when checked in QuickWatch. But as seen in the CommandText property of the command variable 'com', there the insert statement comes out to be: 
insert into SystemDateFormat values('10/05/18')
Now thats again in the dd/yy/MM format.

Anything wrong in my insert query?

Thanks.


Phivos Stylianides replied to Hrushikesh Pande on 18-May-10 05:21 AM
That's because you are converting the date into a string and SQL Server tries to convert it back to the localised system's datetime format. You should use parameterised queries as Sasha suggested so that the datetime object will be automatically converted to the correct format based the current system localisation settings that SQL Server will eventually use.
Super Man replied to Hrushikesh Pande on 18-May-10 05:21 AM

You can use datetime object in sql query

Or if you takes string value of date , then converts into your system specific format.

For example.

Your system takes "yy/MM/dd"

Then convert datetime object into this format.

 

String str = date1.ToString("yy/MM/dd");

Super Man replied to Hrushikesh Pande on 18-May-10 05:21 AM

You can use datetime object in sql query

Or if you takes string value of date , then converts into your system specific format.

For example.

Your system takes "yy/MM/dd"

Then convert datetime object into this format.

 

String str = date1.ToString("yy/MM/dd");

Sasha Kotlo replied to Hrushikesh Pande on 18-May-10 05:45 AM
I'll give you a C# variant of what I meant:

string sqlQuery = "INSERT INTO someTable (dateTimeColumnName) VALUES (@parameterName)";

SqlCommand comm = new SqlCommand(queryString, connObject);
comm.Parameters.AddWithValue("parameterName", DateTimeObject);

connObject.Open();
connObject.ExecuteNonQuery();
conn.Close();

That'll do it. You can read more about using parametrized queries here:

http://www.aspnet101.com/2007/03/parameterized-queries-in-asp-net/

Best regards.
Sasha Kotlo replied to Hrushikesh Pande on 18-May-10 05:45 AM
typo correction:

string sqlQuery = "INSERT INTO someTable (dateTimeColumnName) VALUES (@parameterName)";

string queryString = "INSERT INTO someTable (dateTimeColumnName) VALUES (@parameterName)";
Hrushikesh Pande replied to Sasha Kotlo on 18-May-10 07:14 AM
Hi Sasha,
             Thanks a lot for all those details, the issue is finally resolved.
             Thanks guys for replying and helping me out, that was all very informative.

Thanks.
Sasha Kotlo replied to Hrushikesh Pande on 18-May-10 07:27 AM
No problem I'm glad that I could help you. Best of luck.
Nor replied to Hrushikesh Pande on 14-May-11 03:41 AM
can you give coding how to insert date time into database sql server 2005..
thank you..