How show datetime select only in Sql Server?

Question

I need show datetime when I select datetime only. Because I try to run SQL but show all datetime.

Table Emp:

EmpNo   fullName
00001   Midna
00002   Klog
00003   Porla
00004   Seka
00005   Mila

Table tFile:

EmpNo   cDate                     cTime
00001   2012-10-29 00:00:00.000   2012-10-29 07:52:00.000
00001   2012-10-29 00:00:00.000   2012-10-29 19:00:00.000
00002   2012-10-29 00:00:00.000   2012-10-29 07:40:00.000
00002   2012-10-29 00:00:00.000   2012-10-29 19:32:00.000
00005   2012-10-29 00:00:00.000   2012-10-29 07:58:00.000
00005   2012-10-29 00:00:00.000   2012-10-29 18:35:00.000

This code

SELECT 
   em.EmpNo as 'EmpNo', 
   case 
       when tf.cDate <> null then tf.cDate 
            else coalesce(tf.cDate, '2012-10-29') 
   end as 'cDate', 
   Min(tf.cTime) as 'timeIn', 
   Max(tf.cTime) as 'timeOut'
FROM 
   tFile tf 
Full Outer join 
   Emp em On tf.EmpNo = em.EmpNo
Group By 
   em.EmpNo,tf.cDate
Order By 
   'EmpNo'

returns this result:

EmpNo   cDate                     timeIn                     timeOut
-------------------------------------------------------------------------------------
00001   2012-10-21 00:00:00.000   2012-10-21 07:22:00.000    2012-10-21 17:35:00.000
00001   2012-10-24 00:00:00.000   2012-10-24 07:30:00.000    2012-10-24 19:00:00.000
00001   2012-10-29 00:00:00.000   2012-10-29 07:52:00.000    2012-10-29 19:00:00.000
00002   2012-10-25 00:00:00.000   2012-10-25 07:58:00.000    2012-10-25 18:35:00.000
00002   2012-10-22 00:00:00.000   2012-10-22 08:04:00.000    2012-10-22 17:55:00.000
00002   2012-10-24 00:00:00.000   2012-10-24 08:00:00.000    2012-10-24 18:45:00.000
00002   2012-10-29 00:00:00.000   2012-10-29 07:40:00.000    2012-10-29 19:32:00.000
00003   2012-10-29 00:00:00.000   NULL                       NULL
00004   2012-10-29 00:00:00.000   NULL                       NULL
00005   2012-10-28 00:00:00.000   2012-10-28 07:30:00.000    2012-10-28 19:20:00.000
00005   2012-10-27 00:00:00.000   2012-10-27 07:38:00.000    2012-10-27 19:30:00.000
00005   2012-10-29 00:00:00.000   2012-10-29 07:58:00.000    2012-10-29 18:35:00.000

But I need this result:

I select date ex. 2012-10-29 then I need to show all rows with 2012-10-29 only.

But some Empno don't have data in 2012-10-29 it's set NULL.

EmpNo   cDate                     timeIn                     timeOut
---------------------------------------------------------------------------------------
00001   2012-10-29 00:00:00.000   2012-10-29 07:52:00.000    2012-10-29 19:00:00.000
00002   2012-10-29 00:00:00.000   2012-10-29 07:40:00.000    2012-10-29 19:32:00.000
00003   2012-10-29 00:00:00.000   NULL                       NULL
00004   2012-10-29 00:00:00.000   NULL                       NULL
00005   2012-10-29 00:00:00.000   2012-10-29 07:58:00.000    2012-10-29 18:35:00.000

Thanks for your time. :)

use `cast(fieldname AS date)` to retrieve only the date portion. — Marc B, Oct 29 '12 at 14:52

score 0 · Answer 1 · answered Oct 29 '12 at 14:57

0

how about this?

where(datediff(dd, coalesce(cTime, getdate()), getdate()) = 0

or, if you really only ever want to look at October 29th:

where(datediff(dd, coalesce(cTime, '2012-10-29'), '2012-10-29') = 0

answered Oct 29 '12 at 14:57

paul

21,653
1
53
54

How show datetime select only in Sql Server?

1 Answers1