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

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.

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/

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:

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.

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.

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.

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");

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");

I'll give you a C# variant of what I meant:

typo correction:

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.

No problem I'm glad that I could help you. Best of luck.

can you give coding how to insert date time into database sql server 2005..