asked on

SQL error conversion failed when converting date and/or time from character string

Receiving an error when trying to add this record. It errors out stating that the "conversion failed when converting date and/or time form character string." It will work properly when I manually key in a date for the @SQLWorkDate parameter (e.g. '2017-07-26'). Any idea how I can correct this?

ALTER PROCEDURE [dbo].[AutomaticUpdates_RouteBookDataPreProcessDE]
   @RequestUID TowerRequestUID,
   @SQLWhere PCSTowerReportFilter = NULL,
   @RouteWorkDate DateTime = NULL,
   @WorkDate DateTime = NULL
AS

	DECLARE @SQLRoutes VarChar(MAX)
	DECLARE @SQLCommand VarChar(MAX)
	DECLARE @SQLWorkDate DateTime
	
SET NOCOUNT ON

	SET @WorkDate = (Select DateValue From W_ReportParam Where RequestUID = @RequestUID AND ParamName = 'RouteWorkDate')
	SET @SQLWorkDate = CONVERT(date, @WorkDate)
	SET @SQLRoutes = REPLACE(REPLACE(@SQLWhere, ')', ''), '{Route.Route} IN (', '')
	
	SET @SQLCommand = 'INSERT INTO [dbo].[zRouteBookDataDE] Select ''' + convert(nvarchar(max),@RequestUID) + ''', * From [dbo].[Report_vw_RouteBookDE] Where ServiceDate = ''' + @SQLWorkDate + ''' AND Route IN (' + @SQLRoutes + ') 
		AND NOT(NewStart = 0 AND RouteBookOrder = 0) AND
		NOT( RouteBookOrder = 1 
			And ( 
				ServiceHistoryStatus IN (2,3,4,5,10) Or
				CustomerStatus = 2 Or
				SiteStatus <> ''AP''
				) 
			 )'

Open in new window

Jim Horn

Just for kicks and giggles, what value is being passed for @RequestUID , and what value is returned when this is executed?

Select DateValue From W_ReportParam Where RequestUID = @RequestUID AND ParamName = 'RouteWorkDate'

Open in new window

And is DateValue a date, datetime, or some kind of char?

Sparky1010

ASKER

It is a UniqueID being passed as RequestuID (ex: '2AF33AB3-35E9-48D7-A179-9055C42C5FB3'). DateValue is a datetime (ex: 2017-07-25 00:00:00.000).

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

1. why do you have line 5,

@WorkDate DateTime = NULL

it is set on first line...

2. what is datatype of W_ReportParam.DateValue? any sample?
if it is date you dont need this line

SET @SQLWorkDate = CONVERT(date, @WorkDate)

if it is not, then make sure all of the data here are in datetime format, no nulls, no empty strings, no numbers etc...

Sparky1010

ASKER

This did the trick. Thanks!