CODEWITHSUNDEEP
c#sql-serverdate
greatmajestics
greatmajestics

Reputation: 1093

SQL Server date store in c#

I have a query which fetches the information from sql server on datematch.

I have searched a lot about SQL Server date string, I just want to match with the date and get the data from database. Also I am using SQL Server 2005, I want to fetch the date and take the time out of it?

Can anybody help me in that... I am new to C#

Here is my query. 

return "select Timein, Timeout from Attendance where E_ID = " + E_ID + " and Date = " + DateTime.Now.ToShortDateString();

Upvotes: 0

Views: 318

Answers (2)

Sankara
Sankara

Reputation: 1479

use the sql server CONVERT function to convert the input date param to time Change your query to accommodate any one of the below CONVERT function

SQL query to convert Time format into hh:mm:ss: 

select convert(varchar, <<dateparam>>, 108)

SQL query to convert Time format into hh:mi:ss:mmm(24h): 

select convert(varchar, <<dateparam>>, 114)

Upvotes: 1

marc_s
marc_s

Reputation: 754488

You should always use parameters when querying a database - whether or not SQL injection is possible, it's just plain good practice to use parameters, and it solves some of the thorny how many quotes and which kind do I need here to make it a valid SQL statement questions, too.

So try something like:

string sqlStmt = "SELECT Timein, Timeout FROM dbo.Attendance " + 
                 "WHERE E_ID = @ID AND Date = @Date";

using(SqlConnection conn = new SqlConnection("your-connection-string-here"))
using(SqlCommand cmd = new SqlCommand(sqlStmt, conn))
{
    // set up parameters
    cmd.Parameters.Add("@ID", SqlDbType.Int).Value = E_ID;
    cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now.Date;

    // open connection, read data, close connection
    conn.Open();

    using(SqlDataReader rdr = cmd.ExecuteReader())
    {
       while(rdr.Read())
       {
          // read your data
       }

       rdr.Close();
    }

    conn.Close();
}

Upvotes: 1

Related Questions