How show datetime select only in Sql Server?

  • 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 (or other that datetime).

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

    I don't fix select datetime is 2012-10-29.but it's exam only.

    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. 🙂

  • You have some problems in your query, I'll explain in the code

    SELECT em.EmpNo as 'EmpNo',

    --You should not compare NULLs with = or <> because it will always return unknown and never true

    -- To compare NULLs use IS NULL or IS NOT NULL

    --case when tf.cDate <> null then tf.cDate else coalesce(tf.cDate, '2012-10-29') end as 'cDate',

    --In this case, you don't need the CASE, a simple COALESCE or ISNULL will work

    --I prefere ISNULL where there are only 2 possible values

    ISNULL(tf.cDate, '2012-10-29') as 'cDate',

    Min(tf.cTime) as 'timeIn', Max(tf.cTime) as 'timeOut'

    FROM tFile tf RIGHT OUTER JOIN Emp em On tf.EmpNo = em.EmpNo AND tf.cDate = '20121029'

    --You don't have anything to filter your data. In this case, the filter will be in the JOIN to avoid an INNER JOIN

    -- I turned your FULL JOIN into a RIGHT JOIN

    Group By em.EmpNo,tf.cDate

    Order By 'EmpNo'

    By the way, the design of your table is not correct, you don't need separate columns for date and time since you have information of both in the cTime column. Try to correct that.;-)

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Try this one

    declare @emp table(EMPNo varchar(10),fullnam varchar(20))

    declare @tfile table(EMPNo varchar(10),CDate datetime,Ctime datetime)

    insert into @emp

    select * from

    (

    Values

    ('0001','xyz1'),

    ('0002','xyz2'),

    ('0003','xyz3'),

    ('0004','xyz4'),

    ('0005','xyz5')

    )a (no,name)

    insert into @tfile

    select * from

    (

    Values

    ('0001','10/29/2012','2012-10-29 07:52:00.000'),

    ('0001','10/29/2012','2012-10-29 19:00:00.000'),

    ('0002','10/29/2012','2012-10-29 07:40:00.000'),

    ('0002','10/29/2012','2012-10-29 19:32:00.000'),

    ('0005','10/29/2012','2012-10-29 07:58:00.000'),

    ('0005','10/29/2012','2012-10-29 18:35:00.000')

    )a (no,cdate,ctime)

    -----------------------------------------------------------------------------------------

    select a.EMPNo,cdate,MIN(Ctime) AS timeIn ,MAX(Ctime) AS timeOut

    from @emp a

    LEFT join @tfile b on a.EMPNo=b.EMPNo AND CDate='10/29/2012'

    GROUP BY a.EMPNo,cdate

    ORDER BY A.EMPNo

  • Thanks you so much! Luis Cazares and SSC Rookie.It's work two solution. 😛

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply