asked on

Chnage varchar to Date SQL Server

Hi I need to gather data from the database where an Incident Date is in between the FromDate and ToDate variables. The IncidentDate Column is a varchar field and dates are saved in the following format dd-mm-yyyy, the FromDate and ToDate are initially strings when they are passed across.
The main issue seems to be that the statement is inside a loop and is set to a variable then executed. Please find the Code below :-

@pkLocationID int
,@dataType int
,@ToDate varchar(15)
,@FromDate varchar(15)
,@locationType varchar(200)

AS
BEGIN
SET NOCOUNT ON 


DECLARE @sql nvarchar(2000)
DECLARE @COUNT int
SET @COUNT = 1
Declare @FromDate1 datetime
Declare @ToDate1 datetime
SET @FromDate1 =  CONVERT(datetime, @FromDate,105)
SET @ToDate1 = CONVERT(datetime, @ToDate,105)

WHILE @COUNT < 6
BEGIN
		set @sql = 'SELECT T2.pkRCCauseOptionID as ''RCType'+CAST(@COUNT as varchar(10))+''', CauseOption as ''condition'+CAST(@COUNT as varchar(10))+''', COUNT(RCType) as ''count'+CAST(@COUNT as varchar(10))+'''
		 FROM tblIncidentCause T1
		INNER JOIN tblIncidentRootCauseOptions T2 ON T1.RCType = T2.pkRCCauseOptionID
		INNER JOIN tblIncident t3 on t1.fkIncidentID = t3.pkIncidentID  
		INNER JOIN tblLocation t4 on t3.fkLocationID = t4.pkLocationID  
		INNER JOIN tblIncidentDetails f ON f.fkIncidentID=T3.pkIncidentID 
		WHERE PATINDEX(''%,'+CAST(@pkLocationID as varchar(500))+',%'',Sortkey) > 0 
		AND CONVERT(datetime,t3.IncidentDate,105) >= '+@FromDate1+' AND CONVERT(datetime,t3.IncidentDate,105) <= '+@ToDate1+'
		AND SUBSTRING(CauseOption ,1 , 3) = '''+CAST(@dataType as varchar(10))+'.'+CAST(@COUNT as varchar(10))+'''
		AND t3.fkIncidentType = 21 
		AND t4.Type LIKE ''%'+@locationType+'%''
		AND f.typeofincident IN (1,2,10)
		GROUP BY CauseOption, T2.pkRCCauseOptionID'

		exec sp_executesql @sql
		--print @sql
		SET @COUNT = @COUNT+1 --count up used for loop and within the statement to get data
END

END

Open in new window

With this code I get the following error :- when Msg 241, Level 16, State 1, Procedure SP_v3_incident_report_root_cause_condition_stats_data, Line 34
Conversion failed when converting date and/or time from character string.

I have also tried the following line of code within the WHERE Clause for checking the Incident Date

AND CONVERT(datetime,t3.IncidentDate,105) BETWEEN CONVERT(datetime, '+CAST(@FromDate as varchar(20))+',105) AND CONVERT(datetime, '+CAST(@ToDate as varchar(20))+',105)

Open in new window

This does no flag up an error when being used but also doesn't bring back any data.

Any help will be much appreciated

Lee

Your conversion is wrong.

Try something like this.

cast(right(t3.IncidentDate, 4) + '-' + substring(t3.IncidentDate, 3, 2) + '-' + left(t3.IncidentDate, 2) as datetime) >= '+@FromDate1+' AND cast(right(t3.IncidentDate, 4) + '-' + substring(t3.IncidentDate, 3, 2) + '-' + left(t3.IncidentDate, 2) as datetime) <= '+@ToDate1+'

Open in new window

It converts the string into yyyy-mm-dd and then casts it.

I have't tested it as I wrote this off the top of my head.

Lee

This

substring(t3.IncidentDate, 3, 2)

should be

substring(t3.IncidentDate, 4, 2)

Sevron

ASKER

Hi Thanks for the quick responses I get the following error when I use your code

Operand data type varchar is invalid for subtract operator.

I have tried both :-

substring(t3.IncidentDate, 3, 2)

should be

substring(t3.IncidentDate, 4, 2)

Same error

Lee

I tested mine and it was fine. Can you post your whole query?

Sevron

ASKER

Below is the modified code i may as just missed something silly :-

@pkLocationID int
,@dataType int
,@ToDate varchar(15)
,@FromDate varchar(15)
,@locationType varchar(200)

AS
BEGIN
SET NOCOUNT ON 


DECLARE @sql nvarchar(2000)
DECLARE @COUNT int
SET @COUNT = 1
Declare @FromDate1 datetime
Declare @ToDate1 datetime
SET @FromDate1 =  CONVERT(datetime, @FromDate,105)
SET @ToDate1 = CONVERT(datetime, @ToDate,105)

WHILE @COUNT < 6
BEGIN
		set @sql = 'SELECT T2.pkRCCauseOptionID as ''RCType'+CAST(@COUNT as varchar(10))+''', CauseOption as ''condition'+CAST(@COUNT as varchar(10))+''', COUNT(RCType) as ''count'+CAST(@COUNT as varchar(10))+'''
		 FROM tblIncidentCause T1
		INNER JOIN tblIncidentRootCauseOptions T2 ON T1.RCType = T2.pkRCCauseOptionID
		INNER JOIN tblIncident t3 on t1.fkIncidentID = t3.pkIncidentID  
		INNER JOIN tblLocation t4 on t3.fkLocationID = t4.pkLocationID  
		INNER JOIN tblIncidentDetails f ON f.fkIncidentID=T3.pkIncidentID 
		WHERE PATINDEX(''%,'+CAST(@pkLocationID as varchar(500))+',%'',Sortkey) > 0 
		AND cast(right(t3.IncidentDate, 4) + '-' + substring(t3.IncidentDate, 3, 2) + '-' + left(t3.IncidentDate, 2) as datetime) >= '+@FromDate1+' AND cast(right(t3.IncidentDate, 4) + '-' + substring(t3.IncidentDate, 3, 2) + '-' + left(t3.IncidentDate, 2) as datetime) <= '+@ToDate1+'
		AND SUBSTRING(CauseOption ,1 , 3) = '''+CAST(@dataType as varchar(10))+'.'+CAST(@COUNT as varchar(10))+'''
		AND t3.fkIncidentType = 21 
		AND t4.Type LIKE ''%'+@locationType+'%''
		AND f.typeofincident IN (1,2,10)
		GROUP BY CauseOption, T2.pkRCCauseOptionID'

		exec sp_executesql @sql
		--print @sql
		SET @COUNT = @COUNT+1 --count up used for loop and within the statement to get data
END

END

Open in new window

Lee

You didn't escape your single quotes in the change I made. I also included the change to the substring command as it wasn't picking the correct data out initially.

@pkLocationID int
,@dataType int
,@ToDate varchar(15)
,@FromDate varchar(15)
,@locationType varchar(200)

AS
BEGIN
SET NOCOUNT ON 


DECLARE @sql nvarchar(2000)
DECLARE @COUNT int
SET @COUNT = 1
Declare @FromDate1 datetime
Declare @ToDate1 datetime
SET @FromDate1 =  CONVERT(datetime, @FromDate,105)
SET @ToDate1 = CONVERT(datetime, @ToDate,105)

WHILE @COUNT < 6
BEGIN
		set @sql = 'SELECT T2.pkRCCauseOptionID as ''RCType'+CAST(@COUNT as varchar(10))+''', CauseOption as ''condition'+CAST(@COUNT as varchar(10))+''', COUNT(RCType) as ''count'+CAST(@COUNT as varchar(10))+'''
		 FROM tblIncidentCause T1
		INNER JOIN tblIncidentRootCauseOptions T2 ON T1.RCType = T2.pkRCCauseOptionID
		INNER JOIN tblIncident t3 on t1.fkIncidentID = t3.pkIncidentID  
		INNER JOIN tblLocation t4 on t3.fkLocationID = t4.pkLocationID  
		INNER JOIN tblIncidentDetails f ON f.fkIncidentID=T3.pkIncidentID 
		WHERE PATINDEX(''%,'+CAST(@pkLocationID as varchar(500))+',%'',Sortkey) > 0 
		AND cast(right(t3.IncidentDate, 4) + ''-'' + substring(t3.IncidentDate, 4, 2) + ''-'' + left(t3.IncidentDate, 2) as datetime) >= '+@FromDate1+' AND cast(right(t3.IncidentDate, 4) + ''-'' + substring(t3.IncidentDate, 3, 2) + ''-'' + left(t3.IncidentDate, 2) as datetime) <= '+@ToDate1+'
		AND SUBSTRING(CauseOption ,1 , 3) = '''+CAST(@dataType as varchar(10))+'.'+CAST(@COUNT as varchar(10))+'''
		AND t3.fkIncidentType = 21 
		AND t4.Type LIKE ''%'+@locationType+'%''
		AND f.typeofincident IN (1,2,10)
		GROUP BY CauseOption, T2.pkRCCauseOptionID'

		exec sp_executesql @sql
		--print @sql
		SET @COUNT = @COUNT+1 --count up used for loop and within the statement to get data
END

END

Open in new window

Sevron

ASKER

Hi i have copied the code above out and pasted it directly then run the store procedure as so

/*
 [SP_v3_incident_report_root_cause_condition_stats_data] 26418,1,'01-01-2013','23-12-1970','UK'
*/

Open in new window

Msg 241, Level 16, State 1, Procedure SP_v3_incident_report_root_cause_condition_stats_data, Line 27
Conversion failed when converting date and/or time from character string.

its issues seems to be with the code which initially converts the string into datetime

SET @FromDate1 =  CONVERT(datetime, @FromDate,105)
SET @ToDate1 = CONVERT(datetime, @ToDate,105)

Open in new window

Thanks for all your help so far Lee

Lee

Dont use convert for this. Use cast.

This will be because of the annoying US/UK date style differences. Parse your string and put it into the right format before casting.

SET @FromDate1 =  cast(right(@FromDate, 2) + '-' + substring(@FromDate1, 4, 2) + '-' + left(@FromDate1, 2) as datetime)
SET @ToDate1 =  cast(right(@ToDate1, 2) + '-' + substring(@ToDate1 , 4, 2) + '-' + left(@ToDate1 , 2) as datetime)

Open in new window

Sevron

ASKER

I have tried the following

SET @FromDate1 =  cast(right(@FromDate, 2) + '-' + substring(@FromDate, 4, 2) + '-' + left(@FromDate, 2) as datetime)
SET @ToDate1 =  cast(right(@ToDate ,2) + '-' + substring(@ToDate , 4, 2) + '-' + left(@ToDate , 2) as datetime)

Open in new window

as the @FromDate and @ToDate are the variables which are populated with the string data initially then I try to conveert them and send them into the @FromDate1 and ToDate1

Still getting the same error just multiple times now

Msg 242, Level 16, State 3, Procedure SP_v3_incident_report_root_cause_condition_stats_data, Line 27
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Lee

Can you guarantee that your input data is being put in with the following format?

dd-mm-yyyy

An out of range vaue means it cannot parse the date passed in.

Sevron

ASKER

Hi I think I have figured out why I am having difficulty with the code you have given so far. The Incident Date in Column in the DB Table is a varchar and data within that column is in the following format dd-mm-yyyy. The code you sent over would cast the date in the in the following format yyyy-mm-dd.

I tried :-

cast(left(@FromDate, 2) + '-' + substring(@FromDate, 4, 2) + '-' + right(@FromDate, 4) as datetime)

Open in new window

but I think I need to go back to looking at CONVERT as I am unsure if you can cast the date in that format.

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

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

Start Free Trial

Kevin Cross

Yes, CONVERT(DATETIME, your_varchar_column_or_param, 105) should have worked if your data is in the Italian format dd-mm-yyyy.
MSDN: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Therefore, you should make sure your inputs always match this format as suggested above. One way to verify existing data is make sure your connection/session language is set to Italian, then use ISDATE to find records that SQL cannot parse as a date.

For example:

SET LANGUAGE Italian;
SELECT ISDATE('28-01-2014'); --Returns 1.

Open in new window

Therefore, if you use WHERE ISDATE(your_varchar_column) = 0, you will see the rows that have invalid date values. You can determine what to do from there, or have a way to filter query to rows that have valid dates. Either way, I think it will help.

Kevin Cross

P.S. I agree with using a format like yyyy-mm-dd in character columns, representing dates, because it eliminates errors in data when someone changes the default language of the server. However, I understood the issue as you are running the server with Italian as the default language and users input information this way because you are in that locale. If this is not the case, then you should review why. Otherwise, the CONVERT and ISDATE should work if your setup is correct.