beckyng
asked on
MS sql - Conversion failed when converting date and/or time from character string
Hi
I just created SP as follows, but failed as captioned.
USE [Report]
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 03/19/2012 15:41:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test]
@DB_name nvarchar(50) ,
/****@FromDate datetime,****/
@ToDate datetime,
@Posted nvarchar(3)
AS
BEGIN
SET NOCOUNT ON;
SET @DB_name = RTRIM(@DB_name)
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @DB_name + '.dbo.R_Voucher where Posted in (' + @Posted +') and voucherdate between ' + convert(datetime,@ToDate,4 ) +'and' + @ToDate
EXEC sp_executesql @cmd
END
I just created SP as follows, but failed as captioned.
USE [Report]
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 03/19/2012 15:41:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test]
@DB_name nvarchar(50) ,
/****@FromDate datetime,****/
@ToDate datetime,
@Posted nvarchar(3)
AS
BEGIN
SET NOCOUNT ON;
SET @DB_name = RTRIM(@DB_name)
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @DB_name + '.dbo.R_Voucher where Posted in (' + @Posted +') and voucherdate between ' + convert(datetime,@ToDate,4
EXEC sp_executesql @cmd
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are missing the single quotes inside the quotes - please review my code.
Does http:#a37736713, providing a simple link for the relevant part of the Books Online really qualify as answer here? I have shown exact details of what to use, and it hasn't been just a simple "use the right convert" case.
ASKER
USE [Report]
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 03/19/2012 15:41:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test]
@DB_name nvarchar(50) ,
@FromDate datetime,
@ToDate datetime,
@Posted nvarchar(3)
AS
BEGIN
SET NOCOUNT ON;
SET @DB_name = RTRIM(@DB_name)
DECLARE @cmd AS NVARCHAR(max)
SET @cmd = N'SELECT * FROM ' + @DB_name + '.dbo.R_Voucher where Posted in (' + @Posted +')and voucherdate between ' + convert(varchar,@FromDate,
EXEC sp_executesql @cmd
END