Solved

Date Parameters in SQL SErver

Posted on 2008-10-02
4
151 Views
Last Modified: 2012-05-05
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
Comment
Question by:rustypoot
  • 2
4 Comments
 
LVL 19

Expert Comment

by:folderol
ID: 22629444
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
 

Author Comment

by:rustypoot
ID: 22629833
Thanks so much; I will test it and let you know. THANKS
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 125 total points
ID: 22630265
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
 

Author Closing Comment

by:rustypoot
ID: 31502589
THANKS SO MUCH!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now