Good morning :)

* Using QL Server 2005

I have created a stored procedure (first time I have done this).  It worked
fine when I only had 1 variable (the ProjectCode variable).

However when I add in the PostedDate variable and try and use it I get the
following message.

Msg 8114, Level 16, State 5 Procedure usp_NSJobSummaryRpt, Line 0
Error converting data type nvarchar to datetime.

1) I reviewed the table design for this field and it tells me it is a
datetime field.
2) I put the same SQL (except the Stored Proc. parts) into a Query and ran
and it works (I enter 31/05/2008).  Note when I rerun this in Query I can see
the previous information shows as 31/05/2008 12:00:00 AM.

USE [NAVSQLLIVE_403]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_NSJobSummaryRpt]
@PostedDate datetime,
@ProjectCode varchar(20),
@ProjectAccMgr varchar(50)
AS
SELECT     TOP (100) PERCENT dbo.[Natural Solutions Pty Ltd$Job].[Client
Project Code], dbo.[Natural Solutions Pty Ltd$Resource].Name AS [Project
Manager],
                     dbo.[Natural Solutions Pty Ltd$Client
Project].Description AS [Project Name], dbo.[Natural Solutions Pty
Ltd$Job].No_,
                     dbo.[Natural Solutions Pty Ltd$Job].Description AS
[Job Name], dbo.[Natural Solutions Pty Ltd$Job].[Total Fixed Price],
                     SUM(CASE WHEN dbo.[Natural Solutions Pty Ltd$Job
Ledger Entry].[Resource Type] = 2 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Write Off_Down] = 0 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Total Price] END)
                     AS [Total Labour], SUM(CASE WHEN dbo.[Natural
Solutions Pty Ltd$Job Ledger Entry].[Resource Type] = 2 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Write Off_Down] = 0 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Quantity] END)
                     AS [Total Labour Qty], SUM(CASE WHEN dbo.[Natural
Solutions Pty Ltd$Job Ledger Entry].[Resource Type] <> 2 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Write Off_Down] = 0 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Total Price] END)
                     AS [Total Materials], SUM(CASE WHEN dbo.[Natural
Solutions Pty Ltd$Job Ledger Entry].[Write Off_Down] = 1 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Total Price] END)
                     AS [WriteUp/Off],
                     SUM(CASE WHEN dbo.[Natural Solutions Pty Ltd$Job
Ledger Entry].[Entry Type] = 1 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Total Price]
                      * - 1 END) AS [Invoiced To Date], SUM(CASE WHEN
dbo.[Natural Solutions Pty Ltd$Job Ledger Entry].[Resource Type] = 2 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 AND dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Open] = 1 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Write Off_Down] = 0 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Remaining Amount] END)
                      AS [Labour Outstanding], SUM(CASE WHEN dbo.[Natural
Solutions Pty Ltd$Job Ledger Entry].[Resource Type] = 2 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 AND dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Open] = 1 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Write Off_Down] = 0 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Quantity] END)
                     AS [Labour Qty Outstanding], SUM(CASE WHEN
dbo.[Natural Solutions Pty Ltd$Job Ledger Entry].[Resource Type] <> 2 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 AND dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Open] = 1 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Write Off_Down] = 0 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Remaining Amount] END)
                      AS [Material Outstanding], SUM(CASE WHEN dbo.[Natural
Solutions Pty Ltd$Job Ledger Entry].[Write Off_Down] = 1 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Entry Type] = 0 AND
                     dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Open] = 1 THEN dbo.[Natural Solutions Pty Ltd$Job Ledger
Entry].[Remaining Amount] END)
                     AS [WriteUp/Off Outstanding]
FROM         dbo.[Natural Solutions Pty Ltd$Job Ledger Entry] RIGHT OUTER JOIN
                     dbo.[Natural Solutions Pty Ltd$Job] ON
                     dbo.[Natural Solutions Pty Ltd$Job Ledger Entry].[Job
No_] = dbo.[Natural Solutions Pty Ltd$Job].No_ RIGHT OUTER JOIN
                     dbo.[Natural Solutions Pty Ltd$Resource] RIGHT OUTER
JOIN
                     dbo.[Natural Solutions Pty Ltd$Client Project] ON
                     dbo.[Natural Solutions Pty Ltd$Resource].No_ =
dbo.[Natural Solutions Pty Ltd$Client Project].[Account Manager] ON
                     dbo.[Natural Solutions Pty Ltd$Job].[Client Project
Code] = dbo.[Natural Solutions Pty Ltd$Client Project].Code
WHERE     (dbo.[Natural Solutions Pty Ltd$Job Ledger Entry].[Posting Date] <
@PostedDate)
GROUP BY dbo.[Natural Solutions Pty Ltd$Job].[Client Project Code],
dbo.[Natural Solutions Pty Ltd$Resource].Name,
                     dbo.[Natural Solutions Pty Ltd$Client
Project].Description, dbo.[Natural Solutions Pty Ltd$Job].No_, dbo.[Natural
Solutions Pty Ltd$Job].Description,
                     dbo.[Natural Solutions Pty Ltd$Job].[Total Fixed
Price], dbo.[Natural Solutions Pty Ltd$Client Project].[Project Status]
HAVING      (dbo.[Natural Solutions Pty Ltd$Client Project].[Project Status]
= 0) AND (dbo.[Natural Solutions Pty Ltd$Job].[Client Project Code] =
@ProjectCode)AND
                     (dbo.[Natural Solutions Pty Ltd$Resource].Name =
@ProjectAccMgr)

Can someone please tell me what I need to do to get this working?

Regards

James