SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error converting data type varchar to datetime in stored Procedure


Error converting data type varchar to datetime in stored Procedure

Author
Message
archana 2530
archana 2530
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 12

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
Paul White
Paul White
SSC Guru
SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)

Group: General Forum Members
Points: 126823 Visits: 11444
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
SQLblog.com
@SQL_Kiwi
ChrisM@home
ChrisM@home
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20845 Visits: 10962

,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,




Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
archana 2530
archana 2530
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 12
Hello

I had tried out with 3 of the options.. but still getting Same error.
Paul White
Paul White
SSC Guru
SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)

Group: General Forum Members
Points: 126823 Visits: 11444
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
SQLblog.com
@SQL_Kiwi
ChrisM@home
ChrisM@home
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20845 Visits: 10962
What datatype is E.[ExpanseDate]?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
archana 2530
archana 2530
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 12
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
ChrisM@home
ChrisM@home
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20845 Visits: 10962
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?


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Paul White
Paul White
SSC Guru
SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)SSC Guru (126K reputation)

Group: General Forum Members
Points: 126823 Visits: 11444
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.[LNK_CreatedBy_ID],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
SQLblog.com
@SQL_Kiwi
sami.sqldba
sami.sqldba
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 86
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search