CODEWITHSUNDEEP
c#asp.netsql
Jax
Jax

Reputation: 997

SQL database date format

I have a gridview and sqldatasource. In tabledefinition the default format for date is mm:dd:yyyy , is there any way of changing the format to dd:mm:yyyy from sql settings or something like that.

I have globalization in form load(for dd/mm/.yyyy) but when I'm selecting from datasource some values between some dates :

 string d1 = Convert.ToDateTime(date1.Text).ToString("dd.MM.yyyy");
 string d2 = Convert.ToDateTime(date2.Text).ToString("dd.MM.yyyy");
 SqlDataSource1.SelectCommand = "Select * FROM test WHERE Name = '"+name.Text+"' AND Date between '"+d1+"' AND '"+d2+"'";

It works only for mm/dd/yyyy.

Is there any way to change it?

Upvotes: 0

Views: 2309

Answers (5)

hwcverwe
hwcverwe

Reputation: 5367

Try to use parameters:

SqlDataSource1.SelectCommand = "SELECT * FROM test WHERE Name = @Name AND Date between @DateLow AND @DateHigh";
SqlDataSource1.SelectParameters.Add("Name", name.Text);
SqlDataSource1.SelectParameters.Add("DateLow", DbType.DateTime, d1);
SqlDataSource1.SelectParameters.Add("DateHigh", DbType.DateTime, d2);

Upvotes: 1

Habib
Habib

Reputation: 223402

Its better if you can use Parameters with the query. Also if your column type is Date, then its better if you don't convert them to string for the comparison. Try the following

SqlDataSource1.SelectParameters.Add("@d1", date1);
SqlDataSource1.SelectParameters.Add("@d2", date2);
SqlDataSource1.SelectCommand = "Select * FROM test WHERE Name = '"+name.Text+"' AND Date between @d1 AND @d2";

Also use parameters for Name

Upvotes: 3

Richard
Richard

Reputation: 30628

Avoid the unnecessary conversions back to string and use parameters:

DateTime d1 = Convert.ToDateTime(date1.Text);
DateTime d2 = Convert.ToDateTime(date2.Text);
SqlDataSource1.SelectCommand = "Select * FROM test WHERE Name = @name AND Date between @d1 AND @d2";
SqlDataSource1.SelectCommand.Parameters.AddParameterWithValue("@name", name.Text);
SqlDataSource1.SelectCommand.Parameters.AddParameterWithValue("@d1", d1);
SqlDataSource1.SelectCommand.Parameters.AddParameterWithValue("@d2", d2);

Upvotes: 4

Frederik Gheysels
Frederik Gheysels

Reputation: 56964

Use a parametrized query, and all your problems will disappear. :)

SqlDataSource1.SelectCommand = "Select * FROM test WHERE Name = @p_Name AND Date between @p_from AND @p_to";
SqlDataSource1.SelectCommand.Parameters.Add ("@p_Name", SqlDbType.String).Value = name.Text;
SqlDataSource1.SelectCommand.Parameters.Add ("@p_from", SqlDbType.DateTime).Value = d1;
SqlDataSource1.SelectCommand.Parameters.Add ("@p_to", SqlDbType.DateTime).Value = d2;

Upvotes: 1

Tirumudi
Tirumudi

Reputation: 443

SqlDataSource1.SelectCommand= " SELECT [columns] FROM [table] WHERE Name =   
'"+name.Text+"' AND Date BETWEEN  
CONVERT(Date,'"+d1+"' , 105)  AND CONVERT(Date,'"+d2+"', 105)"

try this....

Upvotes: 0

Related Questions