MS SQL Server Date problem

hi...

i am using MS SQL 2000 as my server in my project.

but when i enter date from vb.net datagrid somtimes it shows me error of char can't be converted to date.Date Overflow problem.

it also has problem with the date modification for date which has month and date values btween

1 - 12 (i.e. like 04/11/2005 and 11/04/2005)

please help me regarding this as i have spend so much time on this but not able to get soluation

Go into the advanced options of your update statement parameters and change the datatype from "Empty" to "DateTime".

|||

how to Go into the advanced options of update statement parameters and change the datatype from "Empty" to "DateTime".

will it cause the problem when i will try to update the data which will be not of the 'datetime' type?

|||

Depends on the tool you are using I suppose. You can also just add the attribute Type="datetime" on the parameter.

And yes, it will cause a problem if you try to update the data if it's not covertable to a datetime type. For example the string "blah" will cause a problem, but the string "2/15/2006" will not cause a problem IF your current culture accepts that as a valid date format (Like en-US).

|||

A workaround for different cultural date settings that I use is to manually build the date string from the different components of the date.

For example, if I need to insert DateTime.Now into the database, I will build the following:

string dateNow = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + " " + DateTime.Now.Hour.ToString() + ":" + DateTime.Now.Minute.ToString() + ":" + DateTime.Now.Second + ".000";

This will build 2006-02-22 11:55:44.000 which will work everytime, no matter what culture settings the user's browser is set to.

On another note, here is a helper method to determine if a string is in a date format (doesn't help with cultural issues though):

public static bool IsDate(object dt)
{
try
{
System.DateTime.Parse(dt.ToString());
return true;
}
catch
{
return false;
}
}