How to filter a DateTime column by Date

Question

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) ";

//

}

Better convert that string to date in SQL. WHERE (CONVERT (DATE, Attendance.in_time) = convert(date,@inDate)) — Azar, Jun 27 '14 at 12:22
@Azar, But I'm doing the comparison in application not in SQL Server! — CAD, Jun 27 '14 at 12:24
Create a new DateTime variable from the year, month, and day components of inTime and do your comparisons to that. — Dan Bracuk, Jun 27 '14 at 12:32

Steve · Accepted Answer · 2014-06-27T14:19:29.530

1

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));

edited Jun 27 '14 at 14:19

answered Jun 27 '14 at 12:31

Steve

213,761
22
232
286

In parameter section it should be InDate.Date and InDate.Date.AddMinutes(1439)). Isn't it? – CAD Jun 27 '14 at 12:48
1

when operating with datetimes in that way, you should NEVER use between. In this case you will miss all rows the last minute of the day – t-clausen.dk Jun 27 '14 at 12:56
@t-clausen.dk Well you are correct of course, but if there are also seconds stored just `AddSeconds(86399)` could resolve the problem. Or not? – Steve Jun 27 '14 at 13:03
@steve that will not solve the problem for milliseconds. Just use column >= fromvalue and column < tovalue – t-clausen.dk Jun 27 '14 at 13:05
1

if you add your current calculation to a datetime you get the next day, due to a feature in datetime you need to add 997 milliseconds. Not really readable. We are back where we started - NEVER use between when operating with datetime intervals. Also things could change the day the system undergo an upgrade to datetime2 – t-clausen.dk Jun 27 '14 at 13:13
1

Well, after a bit of [searching around](http://stackoverflow.com/questions/1630239/sql-between-vs-and) and for the purpose of this discussion I could agree with you. However I think we could agree also that the problem for the poster is that he/she needs two values to search for. A lower bound and an upper bound. This could be given also with Greater Than `>=` and Less Than `<=` – Steve Jun 27 '14 at 13:17

Daniel Gimenez · Answer 2 · 2014-06-27T12:51:31.660

1

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

edited Jun 27 '14 at 12:51

answered Jun 27 '14 at 12:35

Daniel Gimenez

18,530
3
50
70

score 1 · Answer 3 · answered Jun 27 '14 at 12:40

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)

score 0 · Answer 4 · answered Jun 27 '14 at 12:25

0

define inTime like this:

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

answered Jun 27 '14 at 12:25

Erdinç Özdemir

1,363
4
24
52

Now is it not a String? – CAD Jun 27 '14 at 12:28

score 0 · Answer 5 · answered Jun 27 '14 at 12:44

0

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

answered Jun 27 '14 at 12:44

paparazzo

44,497
23
105
176

How to filter a DateTime column by Date

5 Answers5