CODEWITHSUNDEEP
c#sql.netsql-serverdatetime
CAD
CAD

Reputation: 4292

How to filter a DateTime column by Date

My database is SQL Server 2008 and I have a query which returns some details filtered by date. In the WHERE close of the query I have something like this

WHERE (CONVERT (DATE, Attendance.in_time) = @inDate)

NOTE : Attendance.in_time is a DateTime column

Here I'm trying to get the date part only from in_time and to compare it with @inDate parameter in .net.

My problem is, in .NET we cant have only the data part of a DateTime. One option is to convert it to a string as follows

var inDate = InTime.Date.ToString("d");

But now the problem is Date and String cannot be compared in the SQL query?

Can anyone provide a solution?

EDIT : As requested in comments I'm showing full query here ...

 public List<IAttendance> ShowAttendance(DateTime InDate, string pid, List<IAttendance> list)
        {
            string selectStatement = "SELECT Employee.Emp_ID, Employee.Initials + ' ' + Employee.Surname AS Name, Attendance.in_time, Attendance.out_time, Attendance.shift "+
                                     "FROM Attendance INNER JOIN Employee ON Attendance.EID = Employee.Emp_ID "+
                                     "WHERE (CONVERT (DATE, Attendance.in_time) = @inDate) AND (Attendance.PID = @pid) ";

//

}

Upvotes: 0

Views: 3080

Answers (5)

Steve
Steve

Reputation: 216273

You could change your SQL query to exploit the BETWEEN clause

 public List<IAttendance> ShowAttendance(DateTime InDate, string pid, List<IAttendance> list)
 {
    string selectStatement = "SELECT Employee.Emp_ID, Employee.Initials + ' '" + 
                             "Employee.Surname AS Name, Attendance.in_time, " + 
                             "Attendance.out_time, Attendance.shift " +
                             "FROM Attendance INNER JOIN Employee " + 
                             "ON Attendance.EID = Employee.Emp_ID " +
                             "WHERE (Attendance.in_time BETWEEN @inDate AND @endDate) " + 
                             "AND Attendance.PID = @pid";
    ......
 }

and pass two parameters:

cmd.Parameters.AddWithValue("@inDate", InDate.Date);
cmd.Parameters.AddWithValue("@endDate", InDate.Date.AddMinutes(1439));
.....

In this way, the returned records belogs to the same day without the need of complex conversions on the SQL Server side.

As pointed out in the comments below, if there are some values stored after 23.59 you could use AddSeconds(86399) instead of AddMinutes(1439) to enclose also these values. And if you need also milliseconds precision due to the nature of DateTime sql type then

cmd.Parameters.AddWithValue("@endDate", InDate.Date.AddSeconds(86399).AddMilliseconds(999));

Again, due to some comments below. Probably the best option is to use

"WHERE (Attendance.in_time >= @inDate AND Attendance.in_time < @endDate) "

with the parameters defined as 

cmd.Parameters.AddWithValue("@inDate", InDate.Date);
cmd.Parameters.AddWithValue("@endDate", InDate.Date.AddDay(1));

Upvotes: 1

Daniel Gimenez
Daniel Gimenez

Reputation: 20494

Simple solution: Instead of changing your SQL syntax, just pass the InDate.Today as the parameter.

A DateTime at 12:00 am is equal to a Date on the same day.

DECLARE @X DATETIME 
DECLARE @Y DATE

SET @Y = '12/31/2010'
SET @X = '12/31/2010 12:00:00 am'

IF @X = @Y  PRINT 'ok'  -- prints ok

SET @X = '12/31/2010 12:00:01 am'

IF @X != @Y  PRINT 'bad'  -- prints bad

Upvotes: 1

paparazzo
paparazzo

Reputation: 45096

I would break it up into YYYY MM DD
In SQL is it 

YEAR(Attendance.in_time) 
MONTH(Attendance.in_time)
DAY(Attendance.in_time)

In .NET it is properties 

Year
Month
Day

DateTime Properties

All 6 are Int

Upvotes: 0

DiegoSalas
DiegoSalas

Reputation: 43

I do the comparision this way

create  function [dbo].[DateOnly](@DateTime DateTime)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
returns datetime
as
    begin
    return dateadd(dd,0, datediff(dd,0,@DateTime))
    end

and then i compare dates this way

WHERE dbo.dateonly(Attendance.in_time) = dbo.dateonly(@inDate)

Upvotes: 1

Erdin&#231; &#214;zdemir
Erdin&#231; &#214;zdemir

Reputation: 1381

define inTime like this:

var inDate = InTime.Date.ToString("dd-MM-yyyy");

Upvotes: 0

Related Questions