January 3, 2012 at 12:14 am
ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]
@LNK_File_ID numeric(18, 0) = NULL,
@LNK_Client_ID numeric(18, 0) = NULL,
@ExpanseDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT E.[ExpanseId]
,CONVERT(Datetime,E.[ExpanseDate],103) as [ExpanseDate]
,E.[Amount]
,E.[LNK_File_ID]
,E.[LNK_SubFile_ID]
,CONVERT(Datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]
,E.[CreatedOn]
,c.Client_FirstName as ClientName
,f.FileNo
,f.FileName
--,sf.SubFileNo
--,sf.SubFileName
FROM [AFMS].[dbo].[Expanse] E
JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]
JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]
JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]
Where
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End
End
I am Executing this Stored Procedure
exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011 12:00:00 AM'
but I am getting Error
Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0
Error converting data type varchar to datetime.
Archana Mistry
Sr. Programmer
Kintudesigns.com
January 3, 2012 at 12:43 am
Have you tried using a format that is not DATEFORMAT dependent?
The string '29/12/2011 12:00:00 AM' is probably being interpreted as the 12th day of the 29th month 🙂
For example:
'2011-12-29T00:00:00'
'2011-12-29'
'20111229'
See http://msdn.microsoft.com/en-us/library/ms180878.aspx#StringLiteralDateandTimeFormats
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 12:54 am
,CONVERT(Datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
@LNK_Client_ID numeric(18, 0) = NULL,
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 1:21 am
Hello
I had tried out with 3 of the options.. but still getting Same error.
January 3, 2012 at 1:28 am
archana 2530 (1/3/2012)
I had tried out with 3 of the options.. but still getting Same error.
Ok so it's not that then. Have a look at the code lines Chris highlighted. I must admit I didn't get as far as looking at the body of the procedure.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 1:36 am
What datatype is E.[ExpanseDate]?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 5:39 am
Create Table of Expense
USE [AFMS]
GO
/****** Object: Table [dbo].[Expanse] Script Date: 01/03/2012 18:03:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Expanse](
[ExpanseId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[ExpanseDate] [datetime] NOT NULL,
[Amount] [numeric](18, 0) NOT NULL,
[LNK_File_ID] [numeric](18, 0) NOT NULL,
[LNK_SubFile_ID] [numeric](18, 0) NULL,
[LNK_CreatedBy_ID] [numeric](18, 0) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
CONSTRAINT [PK_Expanse] PRIMARY KEY CLUSTERED
(
[ExpanseId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Add Data
-- -----------------------------------------------------------------
-- This script has been automatically generated by SQL Scripter 3.03
-- (unregistered and limited version)
-- 3/1/2012 5:58:34 PM
-- 7 records
-- -----------------------------------------------------------------
SET IDENTITY_INSERT [dbo].[Expanse] ON
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 1)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (1, '20111230', 100, 1, 4, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 1
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 2)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (2, '20111230', 100, 1, 4, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 100, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 2
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 3)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (3, '20111230', 111, 2, NULL, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 111, [LNK_File_ID] = 2, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 3
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 6)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (6, '20111229', 110, 1, NULL, 4, '20111229')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111229', [Amount] = 110, [LNK_File_ID] = 1, [LNK_SubFile_ID] = NULL, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111229' WHERE [ExpanseId] = 6
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 7)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (7, '20111229', 15, 1, 4, 4, '20111229')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111229', [Amount] = 15, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 4, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111229' WHERE [ExpanseId] = 7
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 8)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (8, '20111228', 19, 1, 5, 4, '20111228')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111228', [Amount] = 19, [LNK_File_ID] = 1, [LNK_SubFile_ID] = 5, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111228' WHERE [ExpanseId] = 8
IF NOT EXISTS (SELECT [ExpanseId] FROM [dbo].[Expanse] WHERE [ExpanseId] = 9)
INSERT INTO [dbo].[Expanse] ([ExpanseId], [ExpanseDate], [Amount], [LNK_File_ID], [LNK_SubFile_ID], [LNK_CreatedBy_ID], [CreatedOn])
VALUES (9, '20111230', 15, 3, 6, 4, '20111230')
ELSE
UPDATE [dbo].[Expanse] SET [ExpanseDate] = '20111230', [Amount] = 15, [LNK_File_ID] = 3, [LNK_SubFile_ID] = 6, [LNK_CreatedBy_ID] = 4, [CreatedOn] = '20111230' WHERE [ExpanseId] = 9
SET IDENTITY_INSERT [dbo].[Expanse] OFF
now my Stored Procedure
USE [AFMS]
GO
/****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/03/2012 18:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Created By Archana
-- Create date: 09-12-2011
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]
@LNK_File_ID numeric(18, 0) = NULL,
--@LNK_SubFile_ID numeric(18, 0) = NULL,
@LNK_Client_ID numeric(18, 0) = NULL,
@ExpanseDate datetime = NULL
-- varchar(max)
AS
BEGIN
SET NOCOUNT ON;
SELECT E.[ExpanseId]
,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]
,E.[Amount]
,E.[LNK_File_ID]
,E.[LNK_SubFile_ID]
,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]
,E.[CreatedOn]
,c.Client_FirstName as ClientName
,f.FileNo
,f.FileName
--,sf.SubFileNo
--,sf.SubFileName
FROM [AFMS].[dbo].[Expanse] E
JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]
JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]
JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]
Where
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
--E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID
-- Else E.LNK_SubFile_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End
End
exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011 12:00:00 AM'
I am Executing this Stored Procedure
exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011 12:00:00 AM'
but I am getting Error
Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0
Error converting data type varchar to datetime.
Archana Mistry
Sr. Programmer
Kintudesigns.com
January 3, 2012 at 5:53 am
Run the query outside the stored procedure - always much easier to debug - and chop out some unnecessary bits.
DECLARE
@LNK_File_ID numeric(18, 0),
@LNK_Client_ID numeric(18, 0),
@ExpanseDate datetime
SET @ExpanseDate = '29/12/2011 12:00:00 AM'
SELECT E.[ExpanseId]
,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]
,E.[Amount]
,E.[LNK_File_ID]
,E.[LNK_SubFile_ID]
,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]
,E.[CreatedOn]
,c.Client_FirstName as ClientName
,f.FileNo
,f.FileName
--,sf.SubFileNo
--,sf.SubFileName
FROM [AFMS].[dbo].[Expanse] E
JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]
JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]
JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]
Where
/*
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
--E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID
-- Else E.LNK_SubFile_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
*/
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End
What happens when you run this?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 5:54 am
Thanks, that's very helpful. As Chris noted earlier, the [LNK_CreatedBy_ID] column is defined as [numeric](18, 0) NOT NULL, but the procedure contains CONVERT(datetime,E.[b]LNK_CreatedBy_ID[/b],103) as [LNK_CreatedBy_ID]. Are you intending to try to convert this number to a date/time using style 103?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 6:24 am
Hi dear
Try to put varchar(20) inside the convert function rather then datetime.
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 3, 2012 at 10:18 am
Hii
ChrisM@home Thanks alot for helping me..
and
SQL Kiwi its my mistake u consider as ChrisM@home changes
Archana Mistry
Sr. Programmer
Kintudesigns.com
January 3, 2012 at 10:33 am
You're welcome.
Did you resolve the problem? Would you like to share with us the resolution?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 3, 2012 at 4:16 pm
Archana 2530. Not sure if this helps but I did what sami.sqldba suggested and changed parameter to varchar(25) then added one more statement to your stored proc that converts the date. When I created your table and commented out the joins where I didn't have the tables available I was able to get this to run. It returned the correct two records (those created on 12/29/2011).
USE [AFMS]
GO
/****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/03/2012 18:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Created By Archana
-- Create date: 09-12-2011
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]
@LNK_File_ID numeric(18, 0) = NULL,
--@LNK_SubFile_ID numeric(18, 0) = NULL,
@LNK_Client_ID numeric(18, 0) = NULL,
@ExpanseDate varchar(25) = NULL -- Changed
-- varchar(max)
AS
BEGIN
SET NOCOUNT ON;
SET @ExpanseDate = CONVERT(DATETIME, @ExpanseDate, 103) -- Added
SELECT E.[ExpanseId]
,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]
,E.[Amount]
,E.[LNK_File_ID]
,E.[LNK_SubFile_ID]
,CONVERT(datetime,E.[LNK_CreatedBy_ID],103) as [LNK_CreatedBy_ID]
,E.[CreatedOn]
,c.Client_FirstName as ClientName
,f.FileNo
,f.FileName
--,sf.SubFileNo
--,sf.SubFileName
FROM [AFMS].[dbo].[Expanse] E
JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]
JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]
JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]
Where
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
--E.LNK_SubFile_ID = Case when @LNK_SubFile_ID IS NOT NULL THEN @LNK_SubFile_ID
-- Else E.LNK_SubFile_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End
End
exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011 12:00:00 AM'
January 5, 2012 at 5:13 am
Hi
I have still Problem same.
in stored Procedure Still same Error come.. When I am Executing Stored Procedure
my Actual Procedure is
USE [AFMS]
GO
/****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/05/2012 17:37:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Created By Archana
-- Create date: 09-12-2011
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]
@LNK_File_ID numeric(18, 0) = NULL,
@LNK_Client_ID numeric(18, 0) = NULL,
@ExpanseDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT E.[ExpanseId]
,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]
,E.[Amount]
,E.[LNK_File_ID]
,E.[LNK_SubFile_ID]
,E.[LNK_CreatedBy_ID]
,CONVERT(datetime,E.[CreatedOn],103) as [CreatedOn]
,c.Client_FirstName as ClientName
,f.FileNo
,f.FileName
FROM [AFMS].[dbo].[Expanse] E
JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]
JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]
JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]
Where
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End
End
Execute Procedure
exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011'
Error is
Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0
Error converting data type varchar to datetime.
Archana Mistry
Sr. Programmer
Kintudesigns.com
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply