How show datetime select only in Sql Server?

Question

Post reply

How show datetime select only in Sql Server?

ppc493

SSC Veteran

Points: 290
More actions
October 29, 2012 at 9:16 am

#264803

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

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

BriPan SSCrazy Points: 2061 More actions · Answer 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

ppc493 SSC Veteran Points: 290 More actions · Answer 3

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