Solved

Date Parameters in SQL SErver

Posted on 2008-10-02
4
148 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 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

16 Experts available now in Live!

Get 1:1 Help Now