Solved

Chnage varchar to Date SQL Server

Posted on 2014-01-03
14
309 Views
Last Modified: 2014-01-08
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
0
Comment
Question by:Sevron
  • 7
  • 5
  • 2
14 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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.
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
This

substring(t3.IncidentDate, 3, 2)

should be

substring(t3.IncidentDate, 4, 2)
0
 

Author Comment

by:Sevron
Comment Utility
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
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
I tested mine and it was fine. Can you post your whole query?
0
 

Author Comment

by:Sevron
Comment Utility
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

0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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

0
 

Author Comment

by:Sevron
Comment Utility
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
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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

0
 

Author Comment

by:Sevron
Comment Utility
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.
0
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
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.
0
 

Author Comment

by:Sevron
Comment Utility
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.
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
Comment Utility
You put the date in the format yyyy-mm-dd so that when you convert SQL will always get the date correct when you convert from string to date. If your input is not in the correct format you will get conversion errors. Consider keeping it the way you had it and consider the date:

01-02-2013

Is this the 1st Feb or the 2nd Jan?

You're forcing it to use an Italian format with 105. If you're using a UK date format, use 103 and replace the - with /.

If you put the date in the format yyyy-mm-dd and use cast it will ALWAYS get the date the right way around. If you use dd-mm-yyyy and try it, I guarantee you, you will have problems. The problems arise when the date format can be read more than one way.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now