SQL Server DateTime to String

---

We have already posted SQL DateTime Operations. Here we need another sample which is about how we should compare a SQL Server DateTime and a C# DateTime String:

A sample here:

 StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM ViewAnalogCOSInfo"
                + " WHERE EventStartTime >= '" + StartTime.ToString() + "'"
                + " AND EventEndTime <= '" + EndTime.ToString() + "'");
           ...

If you use above code, you might get error because your SQL Server maybe return you the DateTime String format is "MM/dd/yyyy hh:mm:ss tt", ie: "03/27/2011 07:31:06 PM", but, DateTime.ToString in C# will give you variable format on different computer, for example, in our office, the "StartTime.ToString() " returned format as "MM/dd/yyyy hh:mm:ss tt", but on another computer, it returned for me as "dd/MM/yyyy hh:mm:ss tt", so above code gave us an issue on this computer.

So, we should force the DateTime to string format, we can not ONLY force one side either SQL Server side or C# code side, we need to force both of side can output the same string format.

So, above code should be:

               StringBuilder sb = new StringBuilder();

                sb.Append("SELECT ID, 
                    convert(varchar,[EventStartTime ],120) AS [EventStartTime] ,
                    convert(varchar,[EventEndTime ],120) AS [EventEndTime ] 
                       FROM ViewAnalogCOSInfo"
                + " WHERE EventStartTime >= '" + StartTime.ToString("yyyy-MM-dd HH:mm:ss") + "'"
                + " AND EventEndTime <= '" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");
'120� means datetime format is "yyyy-MM-dd HH:mm:ss" in SQL Server.

OR:

                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM ViewAnalogCOSInfo"
                + " WHERE convert(varchar,[EventStartTime ],120) >= '" + StartTime.ToString("yyyy-MM-dd HH:mm:ss") + "'"
                + " AND convert(varchar,[EventEndTime ],120) <= '" + EndTime.ToString("yyyy-MM-dd HH:mm:ss") + "'");