SQL just check the date with todays Date

Basit Khan

11y1.4k

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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manoj Bhoir11y ago
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.
0
Pankaj Kumar Choudhary11y ago
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

0
Pankaj Kumar Choudhary11y ago
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

0
Jaipal Reddy11y ago
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
0
Liladhar Nimje11y ago
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.
0
Upendra Pratap Shahi11y ago
function checkEmployeePresent(@uid in)
as
begin
select fromdt,todate where uid=@uid
//bring dates to 2 separate variables...
if(todays date> fromdt && todays date< todate)
{
// bring to a variable(on leave)
}
else
{
// not on leave
}
if(todays date> fromdt && todays date< todate)
{
// bring to a variable(on leave)
}
else
{
// not on leave
}
end
0
Khan Abrar Ahmed11y ago
Hi try like this

SELECT *
FROM Trn_EmployeeLeave
WHERE
LeaveFrom <=Today.DATE
AND LeaveUpto >= Today.DATE
AND EmployeeNumber = 1
0
Sreekanth Reddy11y ago
create a User defined function for that:

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