Solved

Convert varchar to TimeDate SQL SERVER out-of-range

Posted on 2014-03-26
4
1,818 Views
Last Modified: 2014-03-26
Hi I previously posted a question about this issue before, the answers I got did fix the problem but its reared its ugly head again.  Have a to and from date which gets passed into the stored procedure and then I use them to count the records with an incident date between the to and from dates passed in, if they meet other conditions with in the statement.

The incident date in the database table is a varchar( I cant change this)

The code is below :-

select COUNT(pkCATSafetyAreaID) AS 'Count', T1.Category, T1.CategoryID from tblIncidentCATSSafetyAreas T1
INNER JOIN tblIncidentCATSUnsafeDetails T2 ON PATINDEX('%,'+CAST(T1.pkCATSafetyAreaID AS varchar(200))+',%',','+T2.SafetyAreas+',') > 0
inner join tblIncident T3 on T2.fkIncidentID = T3.pkIncidentID
inner join Admin_System.dbo.tblLocation T4 on T3.fkLocationID = T4.pkLocationID
WHERE Category = 'Personal Protective Equipment (PPE)'
AND CONVERT(datetime, t3.IncidentDate,105) BETWEEN CONVERT(datetime, @FromDate,105) AND CONVERT(datetime, @ToDate,105)
AND PATINDEX('%,'+cast(@pkLocationID as varchar(100))+',%',Sortkey) > 0
AND T4.Type LIKE '%'+@locationType+'%'
AND t3.ArchivedOn IS NULL
GROUP BY pkCATSafetyAreaID, T1.Category, T1.CategoryID, SafetyArea

The error I get when this is run is :- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Any help with this matter would be much appreciated.
0
Comment
Question by:Sevron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39956012
>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Just for kicks and giggles, execute the below (change to suit your needs) and tell us if you see any values that can't be converted to a date.   There are likely some non-date values that you have to deal with.

SELECT IncidentDate
FROM tblIncident
WHERE ISDATE(IncidentDate) = 0

-- repeat everywhere else you're trying to convert a date
0
 
LVL 34

Expert Comment

by:ste5an
ID: 39956017
The incident date in the database table is a varchar
Your error message clearly shows that this is not true.

You can use the ISDATE() function to filter the valid datetime values. Caveat: Due to optimization this may involve a helper table to filter the valid values first. E.g.

 
SELECT  * ,
        ISDATE(IncidentDate) AS IsValidDateTime
FROM    tblIncident;

Open in new window

0
 

Author Closing Comment

by:Sevron
ID: 39956042
You sir are a legend!!! bad data in the column due to another development adding data into the table.

Cheers
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39956048
Once you have experience as an ETL developer, you learn to never trust source data, and to validate the hell out of it before inserting it into your databases.

Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

752 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