CODEWITHSUNDEEP
c#sql
user3195896
user3195896

Reputation:

How do I convert DateTime into string?

I have a method that requires 3 string so:

public <List> MethodName(string date1, string date2, string number)
{
  //
}

in my database date1 and date2 are stored as DateTime and number is stored as Int so how would I write a SQL query that will search based on these three parameters?

I have converted my date1 and date2 like this: 

DateTime firstdate = Convert.ToDateTime(date1);
string fdate = firstdate.ToLongDateString();
  DateTime seconddate = Convert.ToDateTime(date1);
string sdate = seconddate .ToLongDateString();

My SQL query is: 

  SELECT * From TableName 
  WHERE [Time] > @date1 AND 
  [Time] < @date2 AND [StaffCode] =@StaffCode;

command.Parameters.AddWithValue("@date1", fdate);
command.Parameters.AddWithValue("@date2", sdate );

command.Parameters.AddWithValue("@StaffCode", number);
conn.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        { get the data ...}

I am sure that my SQL query is wrong.

Upvotes: 0

Views: 158

Answers (2)

Suman Banerjee
Suman Banerjee

Reputation: 1961

Conversion:

             DateTime firstdate = Convert.ToDateTime(date1);
             string fdate = firstdate.ToString("yyyy-MM-dd");

             DateTime firstdate2 = Convert.ToDateTime(date2);
             string fdate2 = firstdate2.ToString("yyyy-MM-dd");

SQL Query:

           @"SELECT * From TestTable WHERE Time BETWEEN CONVERT(Date,@date1) AND CONVERT(Date,@date2) AND StaffCode=CONVERT(int,@StaffCode)"
           command.Parameters.AddWithValue("@date1", fdate);
           command.Parameters.AddWithValue("@date2", fdate2);
           command.Parameters.AddWithValue("@StaffCode", number);

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

Since your database columns are already strongly typed, just ensure that your C# parameters are typed as System.DateTime and int (System.Int32) respectively before binding them to your query. Your sql is then simply:

SELECT col1, col2, col3
FROM TableName 
WHERE [Time] > @date1 AND 
      [Time] < @date2 AND [StaffCode] =@StaffCode;

If you allow for inclusive dates, you can use BETWEEN, i.e. 

WHERE [Time] BETWEEN @date1 AND @date2 
    AND [StaffCode] = @StaffCode

i.e. avoid the need to convert a Date to a string altogether. Wherever possible, try and keep a strong type system all the way through your code, both C# and SQL - this will save a lot of pain during conversion. e.g. if possible, see if you can change the signature to:

public List<MethodName>(DateTime date1, DateTime date2, int number)
{
  // Bind the DateTimes, not a string!
  command.Parameters.AddWithValue("@date1", date1);
  command.Parameters.AddWithValue("@date2", date2);
  command.Parameters.AddWithValue("@StaffCode", number);

Edit Don't use SELECT * - use SELECT Col1, Col2, ...

Upvotes: 5

Related Questions