Build a date value

Prev: Query Question - SUM
Next: DB Mail is is not working. Need help please.

From: DavidC on 4 May 2010 17:43

---

I have a stored proc that gets sent a month and year and I would like to turn
it into a date using the day as 1. When I try it I get an error 'Explicit
conversion from data type int to date is not allowed.' Below is my stored
proc. Any help or alternative is appreciated.

ALTER PROCEDURE [dbo].[mc_selPTODetailsForm]
@Month smallint,
@Year smallint,
@AvailableOnly smallint

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @dtYearAgo date;
SET @dtYearAgo = @Month + '/1/' + @Year

SELECT TOP (100) PERCENT
dbo.PTODetails.PTOID,
dbo.People.LastName + N', ' + dbo.People.FirstName AS Worker,
dbo.PTODetails.PeopleLinkID,
dbo.PTODetails.HireDate,
dbo.PTODetails.PTODate,
dbo.PTODetails.PTOGross,
dbo.PTODetails.PTOAmount,
dbo.PTODetails.PTOUnits,
dbo.PTODetails.PTORate,
dbo.PTODetails.PTOBalance,
dbo.PTODetails.PTOUnitsUsed,
dbo.PTODetails.SHC,
dbo.PTODetails.PCW,
dbo.PTODetails.Daily,
dbo.PTODetails.Overnight,
dbo.PeopleLink.Branch,
dbo.PeopleLink.PeopleEnd,
MONTH(dbo.PTODetails.PTODate) AS PTOMonth,
YEAR(dbo.PTODetails.PTODate) AS PTOYear,
dbo.PTODetails.PTOUnits - dbo.PTODetails.PTOUnitsUsed AS
UnitsAvailable
FROM dbo.PTODetails INNER JOIN
dbo.PeopleLink ON dbo.PTODetails.PeopleLinkID =
dbo.PeopleLink.PeopleLinkID INNER JOIN
dbo.People ON dbo.PeopleLink.PersonID = dbo.People.PersonID
WHERE (CASE WHEN @AvailableOnly = 0 AND MONTH(dbo.PTODetails.PTODate) =
@Month AND YEAR(dbo.PTODetails.PTODate) = @YEAR THEN 'T'
WHEN @AvailableOnly = 1 AND dbo.PTODetails.PTOUnits -
dbo.PTODetails.PTOUnitsUsed > 0 THEN 'T'
WHEN @AvailableOnly = 2 AND dbo.PTODetails.PTOUnits -
dbo.PTODetails.PTOUnitsUsed > 0 AND dbo.PTODetails.PTODate <= @dtYearAgo THEN
'T'
ELSE 'F'
END = 'T')
ORDER BY Worker;



--
David

From: DavidC on 4 May 2010 18:06

---

Ignore. I used CAST functions and created it.
--
David


"DavidC" wrote:

> I have a stored proc that gets sent a month and year and I would like to turn
> it into a date using the day as 1. When I try it I get an error 'Explicit
> conversion from data type int to date is not allowed.' Below is my stored
> proc. Any help or alternative is appreciated.
>
> ALTER PROCEDURE [dbo].[mc_selPTODetailsForm]
> @Month smallint,
> @Year smallint,
> @AvailableOnly smallint
>
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> DECLARE @dtYearAgo date;
> SET @dtYearAgo = @Month + '/1/' + @Year
>
> SELECT TOP (100) PERCENT
> dbo.PTODetails.PTOID,
> dbo.People.LastName + N', ' + dbo.People.FirstName AS Worker,
> dbo.PTODetails.PeopleLinkID,
> dbo.PTODetails.HireDate,
> dbo.PTODetails.PTODate,
> dbo.PTODetails.PTOGross,
> dbo.PTODetails.PTOAmount,
> dbo.PTODetails.PTOUnits,
> dbo.PTODetails.PTORate,
> dbo.PTODetails.PTOBalance,
> dbo.PTODetails.PTOUnitsUsed,
> dbo.PTODetails.SHC,
> dbo.PTODetails.PCW,
> dbo.PTODetails.Daily,
> dbo.PTODetails.Overnight,
> dbo.PeopleLink.Branch,
> dbo.PeopleLink.PeopleEnd,
> MONTH(dbo.PTODetails.PTODate) AS PTOMonth,
> YEAR(dbo.PTODetails.PTODate) AS PTOYear,
> dbo.PTODetails.PTOUnits - dbo.PTODetails.PTOUnitsUsed AS
> UnitsAvailable
> FROM dbo.PTODetails INNER JOIN
> dbo.PeopleLink ON dbo.PTODetails.PeopleLinkID =
> dbo.PeopleLink.PeopleLinkID INNER JOIN
> dbo.People ON dbo.PeopleLink.PersonID = dbo.People.PersonID
> WHERE (CASE WHEN @AvailableOnly = 0 AND MONTH(dbo.PTODetails.PTODate) =
> @Month AND YEAR(dbo.PTODetails.PTODate) = @YEAR THEN 'T'
> WHEN @AvailableOnly = 1 AND dbo.PTODetails.PTOUnits -
> dbo.PTODetails.PTOUnitsUsed > 0 THEN 'T'
> WHEN @AvailableOnly = 2 AND dbo.PTODetails.PTOUnits -
> dbo.PTODetails.PTOUnitsUsed > 0 AND dbo.PTODetails.PTODate <= @dtYearAgo THEN
> 'T'
> ELSE 'F'
> END = 'T')
> ORDER BY Worker;
>
>
>
> --
> David

From: Plamen Ratchev on 4 May 2010 18:45

---

DavidC wrote:
> DECLARE @dtYearAgo date;
> SET @dtYearAgo = @Month + '/1/' + @Year

It is better to use language independent date format like YYYYMMDD:

SET @dtYearAgo = CAST(CAST(@Year AS VARCHAR(4)) + RIGHT('00' + CAST(@Month AS VARCHAR(2)), 2) + '01' AS DATE);

>
> SELECT TOP (100) PERCENT

TOP (100) PERCENT is redundant and should be removed.

--
Plamen Ratchev
http://www.SQLStudio.com

 | 
Pages: 1
Prev: Query Question - SUM
Next: DB Mail is is not working. Need help please.