SQL database date format

Question

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?

What is the datatype of column Date in SQL Server ? is it DateTime ? — Habib
– Habib, Commented Jun 15, 2012 at 7:07
A good habit is to use ISO date format in your SQL queries, i.e. YYYYMMDD — Elian
– Elian, Commented Jun 15, 2012 at 7:07
Use Parameters and, having converted the text into dates, keep them as dates. Let ADO.Net translate .NET dates into SQL dates, and avoid dealing with formatting at all — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jun 15, 2012 at 7:11

Richard · Accepted Answer · 2012-06-15 07:13:59Z

4

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

answered Jun 15, 2012 at 7:13

Richard

30.7k12 gold badges79 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Habib · Accepted Answer · 2012-06-15 07:16:40Z

3

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

answered Jun 15, 2012 at 7:16

Habib

224k30 gold badges420 silver badges446 bronze badges

Comments

Frederik Gheysels · Accepted Answer · 2012-06-15 07:12:36Z

1

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;

answered Jun 15, 2012 at 7:12

Frederik Gheysels

57.2k12 gold badges103 silver badges156 bronze badges

1 Comment

Frederik Gheysels Over a year ago

It is what it is; you define a query which has parameters. These act as 'placeholders'. You define what the parameters are, what datatype they should contain, and assign a value to it. After that, all will be taken care of for you (data-formats, quoting, etc... )

hwcverwe · Accepted Answer · 2012-06-15 07:17:44Z

1

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

answered Jun 15, 2012 at 7:17

hwcverwe

5,3677 gold badges37 silver badges64 bronze badges

Comments

Tirumudi · Accepted Answer · 2012-06-15 07:09:28Z

0

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

try this....

answered Jun 15, 2012 at 7:09

Tirumudi

4431 gold badge4 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL database date format

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related