SQL just check the date with todays Date

Question

Dear I have three columns LeaveFrom  LeaveUpto and EmployeeNumber. How i will check with the today date weather the today employees on leave or not. How to do this in SQL please help me. Select * from Trn_EmployeeLeave Where LeaveFrom between Today.Date and Today.Date and LeaveUpto between Today.Date and Today.Date and EmployeeNumber=1 But His leave start from 01-Jun-2015 to 31-Aug-2015 how to check this Thanks Basit

Srikanth Reddy · Answer

create a User defined function for that: function funcname(@uid in) as begin select fromdttodate where uid=@uid //bring dates to 2 separate variables... if(todays date> fromdt && todays date { // bring to a variable(on leave) } else { // not on leave } if(todays date> fromdt && todays date { // bring to a variable(on leave) } else { // not on leave } end

Manoj Bhoir · Answer

Hi If you wat to check this only in SQL Query. Try this : DECLARE @EmployeeNumber INT = 1 SELECT CASE WHEN ((Select COUNT(*) from Trn_EmployeeLeave Where (GETDATE() BETWEEN LeaveFrom and LeaveUpto) and EmployeeNumber = @EmployeeNumber ) = 0) THEN 'PRESENT' ELSE 'ON LEAVE' END You can also check this using C# Code : Execute this query : Select COUNT(*) from Trn_EmployeeLeave Where (GETDATE() BETWEEN LeaveFrom and LeaveUpto) and EmployeeNumber = 1 If count is 0 then employee present else on leave.

Pankaj Kumar Choudhary · Answer

Hello @basit for your query you can use Between Exa: SELECT * FROM Trn_EmployeeLeave Where getdate() between LeaveFrom and LeaveUpto and EmployeeNumber =1 this query give you information about a specific employee SELECT * FROM Trn_EmployeeLeave Where getdate() between LeaveFrom and LeaveUpto This query give you Details about all employee that are on leave today

Pankaj Kumar Choudhary · Answer

Hello @basit for your query you can use Between Exa: SELECT * FROM Trn_EmployeeLeave Where getdate() between LeaveFrom and LeaveUpto and EmployeeNumber =1 this query give you information about a specific employee SELECT * FROM Trn_EmployeeLeave Where getdate() between LeaveFrom and LeaveUpto This query give you Details about all employee that are on leave today

Jaipal Reddy · Answer

Try like this Declare @LeaveFrom datetime Declare @LeaveUpto datetime Set @LeaveFrom ='2015-06-01' Set @LeaveUpto ='2015-06-30' SELECT * FROM Trn_EmployeeLeave Where getdate() between @LeaveFrom and @LeaveUpto and EmployeeNumber = @EmployeeNumber

Liladhar Nimje · Answer

Hi Basit Try the below script: select * from Trn_EmployeeLeave where GETDATE() between LeaveFrom and LeaveUpto It will give you all employees who are on leave today.

Upendra Pratap Shahi · Answer

function checkEmployeePresent(@uid in) as begin select fromdttodate where uid=@uid //bring dates to 2 separate variables... if(todays date> fromdt && todays date { // bring to a variable(on leave) } else { // not on leave } if(todays date> fromdt && todays date { // bring to a variable(on leave) } else { // not on leave } end

Khan Abrar Ahmed · Answer

Hi try like this SELECT * FROM Trn_EmployeeLeave WHERE LeaveFrom AND LeaveUpto >= Today.DATE AND EmployeeNumber = 1