Interview Question : Select List Of those Employee who comes late Office after Office Timing

Anu.Malik
Posted by Anu.Malik under Sql Server category on | Points: 40 | Views : 1581
CREATE TABLE EMPMaster(
[EMP_Name] [varchar](50) NULL,
[InTime] [datetime] NULL
)

Insert INTO EMPMaster Values('AA','2012-07-06 09:33:02.933')
Insert INTO EMPMaster Values('BB','2012-07-06 08:45:02.933')
Insert INTO EMPMaster Values('CC','2012-07-06 10:45:02.933')
Insert INTO EMPMaster Values('DD','2012-07-06 09:33:02.933')
Insert INTO EMPMaster Values('EE','2012-07-06 08:59:02.933')
Insert INTO EMPMaster Values('FF','2012-07-06 10:33:02.933')

Declare @OfficeTime Varchar(10)
Set @OfficeTime='09:00AM'

SELECT * FROM EMPMaster WHERE CONVERT(VARCHAR,GETDATE(),101)=CONVERT(VARCHAR,InTime,101) AND
RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,InTime,100),8)),7) > @OfficeTime

Comments or Responses

Posted by: Jasminej on: 7/6/2012 Level:Starter | Status: [Member] | Points: 10
You can also simple use like...
Declare @OfficeTime Varchar(10)


Set @OfficeTime='09:00AM'

Select * from EMPMaster
Where [InTime] > Cast(CONVERT(Varchar(10), GetDate(),121) + ' ' + @OfficeTime as Datetime)

Posted by: Anu.Malik on: 7/6/2012 Level:Starter | Status: [Member] | Points: 10
Yes Nice

Login to post response