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
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.
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)
-- http://www.sqlscripter.com
-- 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?
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy