• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Date Parameters in SQL SErver

In my Stored Proc, I have 2 Date parameters - @BeginningPayDate DateTime, and @EndingPayDate DateTime. These are used in Where Clause as - T7.prgPayDate BETWEEN @BeginningPayDate and @EndingPayDate.

I need to remove the parameter @BeginningPayDate DateTime. In my where clause, I need to do below:
T7.prgPayDate BETWEEN 'Jan 1st of the @ EndingPayDate' and @EndingPayDate.

How can I do it? I need to replace @BeginningPayDate DateTime with January 1st of the @EndingPayDate.

Please help. Thanks for the Help.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
-- =============================================
-- Author:        <Shah,Sony>
-- Create date:   <07/9/2008>
-- Description:   <Payroll Earnings>
-- =============================================
 
ALTER Procedure [dbo].[sproc_Payroll_Earnings_Current_YTD]
(
@BeginningPayDate DateTime,
@EndingPayDate DateTime,
@Login Varchar (250),
@Company Varchar (3500),
@Location Varchar (3500)
)
AS
BEGIN
SET NOCOUNT ON
 
select 	DISTINCT
T1.CmpCompanyName, 
T1.CmpCOID,
T9.LocCode,
T9.LocDesc, 
T3.EjhReason, 
T3.EjhEmplStatus, 
T3.EjhFullTimeOrPartTime, 
T3.EjhEEType,
T3.EjhOrglvl2 as GL_Dept_Code,
T3.EjhOrglvl4,
T3.EjhOrglvl1,
T3.EjhJobCode,
T3.EjhJobDesc,
T4.EecEmpNo,
T4.EecDateOfOriginalHire,
T4.EecDateOfTermination,
T4.EecDateOfLastHire,
T4.EecScheduledWorkHrs,
T4.EecSalaryOrHourly,
T4.EecAnnSalary ,
T55.EepNameFirst,
T55.EepNameLast,
T5.EepNameLast as SupervisorNameLast,
T5.EepNameFirst as SupervisorNameFirst,
T6.OrgDesc as GL_Dept_Desc,
T8.PehCurHrs,
T8.PehPayRate,
T8.PehCurAmt,
T8.PehYTDAmt,
T10.ErnEarncode,
T10.ErnStubDesc, 
T7.PrgTotHours,
T7.PrgTotEarnAmt,
T7.prgPayDate
from 
ULTIPRO_FREE.dbo.Company T1 JOIN 
(
    select b.* from 
      (
      select ejhcoid,ejheeid,max(ejhdatetimecreated) as ejhdatetimecreated from ULTIPRO_FREE.dbo.EmpHJob 
      group by ejhcoid,ejheeid
      ) a
      join ULTIPRO_FREE.dbo.emphjob b
      on a.ejhcoid = b.ejhcoid and
      a.ejheeid = b.ejheeid and
      a.ejhdatetimecreated = b.ejhdatetimecreated
) T3
ON T1.CmpCoID = T3.EjhCoID
JOIN ULTIPRO_FREE.dbo.EmpComp T4
ON T3.EjhCoID = T4.EecCoID and 
   T3.EjhEeID = T4.EecEEID 
JOIN ULTIPRO_FREE.dbo.EmpPers T5
ON   T4.EecSupervisorID = T5.EepEEID
JOIN ULTIPRO_FREE.dbo.EmpPers T55
ON T4.eecEeId = T55.eepEeId
LEFT OUTER JOIN ULTIPRO_FREE.dbo.OrgLevel T6
ON T3.EjhOrgLvl2 = T6.OrgCode and 
   T6.OrgLvl = 2 
JOIN ULTIPRO_FREE.dbo.PayReg T7
ON T4.EecCoID = T7.PrgCoID and 
   T4.EecEEID = T7.PrgEEID
JOIN ULTIPRO_FREE.dbo.PEarHist T8
ON T7.PrgEEID = T8.PehEEID and
   T7.PrgCOID = T8.PehCOID and 
	T7.PrgGenNumber = T8.PehGenNumber  
JOIN ULTIPRO_FREE.dbo.Location T9
ON T4.EecLocation = T9.LocCode 
JOIN ULTIPRO_FREE.dbo.EarnCode T10
ON T8.PehEarnCode= T10.ErnEarncode
JOIN dbo.FREEDOM_SECLIST s
on  T1.cmpcoid = s.Company and
T9.loccode = s.Location and
T6.orgcode = s.OrgLevel and
s.logon = @Login
where 
T8.PehCurAmt <> 0 and 
T8.PehShfShiftCode = 'Z'and
T7.prgPayDate BETWEEN @BeginningPayDate and @EndingPayDate and
T4.EecEmpNo NOT LIKE 'T%' and
T1.cmpcoid IN (Select * from ULTIPRO_FREE.dbo.Freedom_Company_fnSplit(@Company, '^'))and 
T9.LocCode IN (Select * from ULTIPRO_FREE.dbo.Freedom_Company_fnSplit(@Location, '^'))
--T1.CmpCompanyName like 'Daily Press%' --and 
--T55.EepNameLast like 'Y%' --and
--T7.prgPayDate >= '2008-01-01'
 
END

Open in new window

0
rustypoot
Asked:
rustypoot
  • 2
1 Solution
 
folderolCommented:
This formula always returns january 1 of the same year as the input date.

set @BeginningPayDate = dateadd(year,datediff(year,0,@EndingPayDate),0)

Below is your function with the appropriate changes....
ALTER Procedure [dbo].[sproc_Payroll_Earnings_Current_YTD]
(
@EndingPayDate DateTime,
@Login Varchar (250),
@Company Varchar (3500),
@Location Varchar (3500)
)
AS
BEGIN
SET NOCOUNT ON
declare @BeginningPayDate DateTime
set @BeginningPayDate = dateadd(year,datediff(year,0,@EndingPayDate),0) 
select 	DISTINCT
T1.CmpCompanyName, 
T1.CmpCOID,
T9.LocCode,
T9.LocDesc, 
T3.EjhReason, 
T3.EjhEmplStatus, 
T3.EjhFullTimeOrPartTime, 
T3.EjhEEType,
T3.EjhOrglvl2 as GL_Dept_Code,
T3.EjhOrglvl4,
T3.EjhOrglvl1,
T3.EjhJobCode,
T3.EjhJobDesc,
T4.EecEmpNo,
T4.EecDateOfOriginalHire,
T4.EecDateOfTermination,
T4.EecDateOfLastHire,
T4.EecScheduledWorkHrs,
T4.EecSalaryOrHourly,
T4.EecAnnSalary ,
T55.EepNameFirst,
T55.EepNameLast,
T5.EepNameLast as SupervisorNameLast,
T5.EepNameFirst as SupervisorNameFirst,
T6.OrgDesc as GL_Dept_Desc,
T8.PehCurHrs,
T8.PehPayRate,
T8.PehCurAmt,
T8.PehYTDAmt,
T10.ErnEarncode,
T10.ErnStubDesc, 
T7.PrgTotHours,
T7.PrgTotEarnAmt,
T7.prgPayDate
from 
ULTIPRO_FREE.dbo.Company T1 JOIN 
(
    select b.* from 
      (
      select ejhcoid,ejheeid,max(ejhdatetimecreated) as ejhdatetimecreated from ULTIPRO_FREE.dbo.EmpHJob 
      group by ejhcoid,ejheeid
      ) a
      join ULTIPRO_FREE.dbo.emphjob b
      on a.ejhcoid = b.ejhcoid and
      a.ejheeid = b.ejheeid and
      a.ejhdatetimecreated = b.ejhdatetimecreated
) T3
ON T1.CmpCoID = T3.EjhCoID
JOIN ULTIPRO_FREE.dbo.EmpComp T4
ON T3.EjhCoID = T4.EecCoID and 
   T3.EjhEeID = T4.EecEEID 
JOIN ULTIPRO_FREE.dbo.EmpPers T5
ON   T4.EecSupervisorID = T5.EepEEID
JOIN ULTIPRO_FREE.dbo.EmpPers T55
ON T4.eecEeId = T55.eepEeId
LEFT OUTER JOIN ULTIPRO_FREE.dbo.OrgLevel T6
ON T3.EjhOrgLvl2 = T6.OrgCode and 
   T6.OrgLvl = 2 
JOIN ULTIPRO_FREE.dbo.PayReg T7
ON T4.EecCoID = T7.PrgCoID and 
   T4.EecEEID = T7.PrgEEID
JOIN ULTIPRO_FREE.dbo.PEarHist T8
ON T7.PrgEEID = T8.PehEEID and
   T7.PrgCOID = T8.PehCOID and 
	T7.PrgGenNumber = T8.PehGenNumber  
JOIN ULTIPRO_FREE.dbo.Location T9
ON T4.EecLocation = T9.LocCode 
JOIN ULTIPRO_FREE.dbo.EarnCode T10
ON T8.PehEarnCode= T10.ErnEarncode
JOIN dbo.FREEDOM_SECLIST s
on  T1.cmpcoid = s.Company and
T9.loccode = s.Location and
T6.orgcode = s.OrgLevel and
s.logon = @Login
where 
T8.PehCurAmt <> 0 and 
T8.PehShfShiftCode = 'Z'and
T7.prgPayDate BETWEEN @BeginningPayDate and @EndingPayDate and
T4.EecEmpNo NOT LIKE 'T%' and
T1.cmpcoid IN (Select * from ULTIPRO_FREE.dbo.Freedom_Company_fnSplit(@Company, '^'))and 
T9.LocCode IN (Select * from ULTIPRO_FREE.dbo.Freedom_Company_fnSplit(@Location, '^'))
--T1.CmpCompanyName like 'Daily Press%' --and 
--T55.EepNameLast like 'Y%' --and
--T7.prgPayDate >= '2008-01-01'
 
END

Open in new window

0
 
rustypootAuthor Commented:
Thanks so much; I will test it and let you know. THANKS
0
 
BrandonGalderisiCommented:
To do current year, and not have to change it every year make the following change:

T7.prgPayDate >= '2008-01-01'
to
datepart(y,t7.prgpaydate)=year(getdate())
0
 
rustypootAuthor Commented:
THANKS SO MUCH!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now