CODEWITHSUNDEEP
sql-servervb.net
Rabeea qabaha
Rabeea qabaha

Reputation: 606

select where date (only date from datetime)

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

Answers (2)

Charles Bretana
Charles Bretana

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

Joel Coehoorn
Joel Coehoorn

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

Some notes about this code

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

Related Questions