SQL CONVERT function not working

I can't pull any examples at the moment, but I recall that ONE of the "convert" functions - either CAST or CONVERT - doesn't work (or work reliably) within ReportWriter.

IF it works within MS SQL Management Studio, but not in ReportWriter, then try using CAST instead and see if it works.

Leon,

Thanks for the tip!  It took me a little while to get it right but I got the CAST to work in this way. 

SELECT TOP 10
  CustomPollers.UniqueName AS PollerName
, CustomPollerStatus.Status AS Status
, cast(CustomPollerStatus.Status as date) as CastDate
, convert(varchar(10), cast(CustomPollerStatus.Status as date), 112) as YYYYMMDD
, convert(varchar(10),DATEADD(year,-3,getdate()), 112) as TodaysDateMinus3Years

FROM CustomPollerStatus

JOIN CustomPollerAssignment ON CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
JOIN CustomPollers ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID

WHERE (CustomPollers.UniqueName = 'APCupsBasicBatteryLastReplaceDate')

ORDER BY convert(varchar(10), cast(CustomPollerStatus.Status as date), 112) ASC

Report Writer results:

Now, moving on, I'm having trouble when adding the ORDER BY clause. I'm getting "SQL error: Conversion failed when converting date and/or time from character string.". 

I used the ISDATE function to verify CustomPollerStatus.Status and each row returned the value 1 (valid) thinking there may be an invalid date.

I need to be able to sort by the CAST or CONVERT date result (yyyymmdd).  I've also tried using a CASE like the following.  Any thoughts how to resolve this issue?

ORDER BY

CASE

WHEN ISDATE(CustomPollerStatus.Status)  = 1 THEN convert(varchar(10), cast(CustomPollerStatus.Status as date), 112)

ELSE NULL

END ASC

Have you tried the order by on the plain date? Just as a test?

Sorry, I forgot to mention that.  I did try  ORDER BY CustomPollerStatus.Status ASC  and it returns the same SQL error result.

I'll look into the original ORDER BY syntax, which seems that it should be valid on the surface since it's used in a SELECT field.

ORDER BY convert(varchar(10), cast(CustomPollerStatus.Status as date), 112) ASC

But I think the issue may be the attempt to convert a CAST() value.

You're taking CustomPollerStatus.Status, casting it as a date value, and then trying convert it back to a varchar with date format '112'.

It works in the SELECT, but I think it may be problematic in the ORDER BY.

In the second example, you can't use CASE logic in the ORDER BY, so that explains that.

I think all you need to do is just sort based on the original CAST():

ORDER BY  CAST(CustomPollerStatus.Status as date) ASC

because it is a date value,it will sort in chronological order automatically.

Thanks for responding LGarvin!

ORDER BY  CAST(CustomPollerStatus.Status as date) ASC also throws the same error  "SQL error: Conversion failed when converting date and/or time from character string.".