Rohini Mathur Rohini Mathur - 2 months ago 13
SQL Question

Data type conversion error in stored procedure in SQL Server 2008

May someone please help me to resolve this error.

Table structure:

DECLARE @TABLE TABLE (MYDATE DATETIME2)


INSERT @TABLE
SELECT 2016-08-08 10:27:58.0000000 UNION ALL
SELECT 2016-09-25 16:55:00.0000000 UNION ALL
SELECT 2016-01-07 10:09:00.0000000 UNION ALL
SELECT 2016-01-07 10:10:00.0000000 UNION ALL
SELECT 2016-02-26 16:55:00.0000000


I don't have permission to alter this table's structure.

My stored procedure:

CREATE PROCEDURE dbo.CHECKS
@MYDATE DATETIME
AS
SELECT
*
FROM
TABLE1 t1
INNER JOIN
TABLE2 t2
WHERE
CONVERT(NVARCHAR(MAX), @MYDATE, 105) = MYDATE


But client want to pass date parameter like

EXEC dbo.ComparePrices '25092016'


While doing so getting error like


Msg 8114, Level 16, State 1, Procedure ComparePrices, Line 0

Error converting data type varchar to datetime.


Please help me.

Thanks

Answer

You can use STUFF() to insert delimiters into the rigth positions. Then you can use CONVERT() with the appropriate format. In this case I insert / to get the format 103. Find details here:

DECLARE @stringdate VARCHAR(100)='30092016';
SELECT CONVERT(DATETIME,STUFF(STUFF(@stringdate,3,0,'/'),6,0,'/'),103)

EDIT: Sorry, STUFF() needs SQL Server 2012+...

With 2008 you can use simple string methods

SELECT CONVERT(DATETIME,LEFT(@stringdate,2)+'/'+SUBSTRING(@stringdate,3,2)+'/'+RIGHT(@stringdate,4),103)