Reputation: 606
how can I search for a records in the database (sql server) where date dd/mm/yyyy from date format dd/mm/yyyy h:mm:ss .
if the column is date it's work fine but when I make it datetime and want to see if there's records in database in specific date it's different because there's time.
so how can i search for the date only when the column is datetime.
this is what i'm using:
If SQL.conn.State = ConnectionState.Open Then
SQL.conn.Close()
End If
DateTime1.Format = DateTimePickerFormat.Custom
DateTime1.CustomFormat = "yyyy/MM/dd"
SQL.conn.Open()
Dim cmd As New SqlCommand("Select count(eid) from employees_payroll WHERE eid=" & employee_id & " and wday= '" & DateTime1.Text & "'", SQL.conn)
count = Convert.ToInt32(cmd.ExecuteScalar())
SQL.conn.Close()
DateTime1.CustomFormat = "yyyy-MM-dd"
Upvotes: 2
Views: 4767
Reputation: 146603
Several solutions. Cast datetime to date this eliminates the time
Select * from table where cast (column as Date) = '1 jan 2017'
second, use date diff function
Select * from table where datediff(day, column, '1Jan2017') = 0
third, strip time portion using datediff and dateadd
Select * from table
where dateadd(day, datediff(day, 0, column), 0) = '1Jan2017'
also, compare to midnight at start and end of the day
Select * from table
where column >= '1 Jan 2017'
and column < '2 jan 2017'
Last solution is only one that allows use of index on the date column
EDIT ( to address concerns raised by Joel in comments): Please understand that the C# code you are using to pass this query to the Server is vulnerable to what is called "SQL Injection". If your application is constructing this SQL from input from untrusted users, this is an invitation to disaster. As Joe; suggests in his answer below, the C# code should be modified to use command objects with defined parameters, instead of dynamically constructed SQL. The SQL statement itself, however can still be any of the solutions suggested above.
Upvotes: 3
Reputation: 416179
Using cn As New SqlConnection("connection string here"), _
cmd As New SqlCommand("Select count(eid) from employees_payroll WHERE eid= @employee_id and wday >= @wday and wday < DATEADD(dd, 1, @wday)", cn)
cmd.Parameters.Add("@employee_id", SqlDbType.Int).Value = Convert.ToInt32(employee_id)
cmd.Parameters.Add("@wday", SqlDbType.DateTime).Value = DateTime1.Value
cn.Open()
count = Convert.ToInt32(cmd.ExecuteScalar())
End Using
In .Net, you really do want to use a whole new connection object for for most operations. It's a mistake to try to re-use the same connection object throughout your application. Really. The ADO.Net provider for SQL Server uses something called connection pooling, and you lose the benefits of that when you stick with the same object in your app. Instead of a sharing a common connection object, just share a common connection string for creating the new object when you need it. Then you can also wrap things in a Using
block, so that this connection is properly closed as needed, even if an exception is thrown. Note that I don't have to cn.Close()
in this sample, and that's not a mistake. The Using
block will take care of this properly.
Don't use string concatenation to put data values into an sql command statement! There are several reasons for this, ranging from performance (you get execution plan re-use and better query statistics) to security (no more sql injection vulernability!) to fixing formatting issues like the one you have here. Even though this looks like private winforms app, rather than public web app, you still need to use parameterized queries for this.
When you start using DateTime
values in the database, there is always a time component in your column value, and Sql Server always compares those values using the full date and time, no exeptions. If you don't specify a time, Sql Server uses a "0" time value, meaning midnight. So comparing two "Date"s in a DateTime
column only works if both sides of the comparison have exactly the same time values. If you want to know if a DateTime value falls on a specific day, the correct way to do this is the check if it's greater than or equal to the start of the day and less than the start of the next day. If you wanted to, you could instead cast your wday
column to a Date
type for this query. I encourage you not to do this, as it will prevent Sql Server from using any indexes that might exist for that column.
Upvotes: 1