date format issue with asp.net and sql server

Question

when i'm inserting data to the sql server 2008 database through asp.net application date inserted to the database in the following format

"10 june 2011"

this is wrong and i need "dd/mm/yyyy" or "yyyy/mm/dd" format to be inserted.why does this happen

Answer 1

If your data type on the database side is either datetime or date (on sql server 2008), it shouldn't matter whether you insert the date as '10 june 2011' or as '6/10/2011' or as '2011-06-10'. If you see the data actually being displayed as '10 June 2011' that's more likely because your data type is varchar or nvarchar. If that's the case and the field is only meant to hold dates, I would advise you to change the data type to be actually a datetime or date.

Answer 2

Use DateTime.TryParseExact() method to parse the date.

string inputDate="10 June 2011";
string []format ={ "dd MMMM yyyy","dd MMM yyyy"};
DateTime date;

if (DateTime.TryParseExact(inputDate, format, CultureInfo.InvariantCulture, 
                           DateTimeStyles.None, out date))
 {
     Console.WriteLine("Valid " + date);
 }

Answer 3

Table column datatype was Nvarchar, so that it saves data in the above format which is wrong. By running the following I have solved my issue

SET DATEFORMAT DMY;

ALTER TABLE #tmpTest ALTER COLUMN MyCol DATE;