|
|
|
date: Tue, 10 Jun 2008 17:56:01 -0700,
group: microsoft.public.sqlserver.mseq
back
Error converting data type nvarchar to datetime
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
date: Tue, 10 Jun 2008 17:56:01 -0700
author: James Terrington
Re: Error converting data type nvarchar to datetime
James,
Well, the message is not that helpful, but it is a matter of your Locale.
This is influenced by your language settings in SQL Server. For example:
SET LANGUAGE us_english
exec [usp_NSJobSummaryRpt] '31/05/2008','1','2'
Msg 8114, Level 16, State 1, Procedure usp_NSJobSummaryRpt, Line 0
Error converting data type varchar to datetime.
SET LANGUAGE British
exec [usp_NSJobSummaryRpt] '31/05/2008','1','2'
Command(s) completed successfully.
In other words, the character string is not recognized (in us_english) as a
valid character string, so it fails to convert. Ideally, when using
character strings you should use universally recognized format such as:
'20080531' or '2008-05-31T00:00:00'.
Other people may have better internationalization guidance.
RLF
"James Terrington" wrote in
message news:DF8FC033-78E3-4E55-B2A1-FE60C1D2E764@microsoft.com...
> 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
date: Wed, 11 Jun 2008 14:01:37 -0400
author: Russell Fields
|
|
