CODEWITHSUNDEEP
sqlsql-server
BSanders
BSanders

Reputation: 295

converting to uniqueidentifier in SQL

I need the following to be a uniqueidentifier so that it will join properly in a larger query. Is there a better way to be doing this?

Here is the SQL

 -- Report Totals
-- Set date range and this query will look at each specific Crystal Report and how long each one takes to run.  
DECLARE @StartDate DATETIME,
      @EndDate DATETIME;


SELECT @StartDate = '10/01/2013', @EndDate = '1/1/2014';

SELECT COUNT(*) AS RunCount, AVG(DATEDIFF(s, SJ.queuedtime, SJ.completetime)) AS TotalTime,
       AVG(DATEDIFF(s, SJ.queuedtime, SJ.starttime)) AS WaitTime,
       AVG(DATEDIFF(s, SJ.starttime, SJ.completetime)) AS RunTime, 
         RP.label AS Report, RP.reportTitle

FROM SJob SJ

JOIN RReport R ON CAST(SUBSTRING(SJ.props, CHARINDEX('reportID=', SJ.props, 0) + 9, 36) AS UNIQUEIDENTIFIER) = R.reportID
JOIN RPhysicalReport RP ON R.physicalReportID = RP.physicalReportID

WHERE SJ.queuedtime >= @StartDate and SJ.queuedtime < @EndDate and SJ.jobClass = 'CRWPrint'

GROUP BY RP.label, RP.reportTitle

ORDER BY RunTime DESC

when I run this I get

Msg 8169, Level 16, State 2, Line 9 Conversion failed when converting from a character string to uniqueidentifier.

Upvotes: 0

Views: 6100

Answers (2)

BSanders
BSanders

Reputation: 295

this was solved by using CONVERT(VARCHAR(36), R.reportID) in my JOIN clause. It now works as it should.

Upvotes: 1

Registered User
Registered User

Reputation: 8395

There are likely one of two problems:

  1. You aren't getting all 36 characters because there are trailing characters after reportID.
  2. The GUID is not properly formatted, likely because of missing dashes or incorrect position of dashes.

Here's some code that could resolve the first issue assuming the value after reportID= is a valid GUID:

DECLARE @SomeString VARCHAR(MAX) = 'sometextwithar=somethingelse&reportID=5289A1C3-07E3-4CE3-B2C4-78E6B631458E&something';

SELECT CONVERT(UNIQUEIDENTIFIER, SUBSTRING(@SomeString, CHARINDEX('reportID=', @SomeString, 0) + 9, 36)) AS reportID;

I would recommend removing the CAST to UNIQUEIDENTIFIER section and inspect the string you are trying to convert.

Upvotes: 3

Related Questions