6 Replies Latest reply on Nov 16, 2013 7:52 AM by rgward

    SQL CONVERT function not working

    rgward

      I have a custom poller that polls an OID (APCupsBasicBatteryLastReplaceDate) that contains a date value in mm/dd/yy format.  What is really strange and has me stumped is that I'm not having any success in converting the date value to any other date style.  To demonstrate this issue, I've created the SQL below.  I'm trying to convert the date format to yymmdd so I can later compare (CONVERT omitted from WHERE clause) for older than 3 years (Date_minus_3years).  Also, notice the Report Writer result doesn't respect the 'AS ReplacementDate' clause and the column heading remains the table column name CustomPollerStatus.Status, not 'ReplacementDate'.  The CONVERT and DATEADD using current getdate() works fine and generates the right date result (today's date 131012 minus 3 years).  But, I'm stumped why the CONVERT function (highlighted in bold) is being ignored and returns the original table column name (Status) and date format (mm/dd/yy).  From what I see, the CustomPollerStatus.Status column is defined in the table as nvarchar(1000). 

       

      Can anyone explain why the CONVERT function is not working?  What am I missing?

       

      SELECT  CustomPollers.UniqueName AS PollerName
      , convert(varchar(10),CustomPollerStatus.Status, 12) AS ReplacementDate
      , convert(varchar(10),DATEADD(year,-3,getdate()), 12) as Date_minus_3years
      FROM CustomPollerStatus

       

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

       

      WHERE (CustomPollers.UniqueName = 'APCupsBasicBatteryLastReplaceDate')


      Report Writer Result

      SQL Date Conversion not working in Report Writer_101213.PNG

        • Re: SQL CONVERT function not working
          Leon Adato

          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.

          1 of 1 people found this helpful
            • Re: SQL CONVERT function not working
              rgward

              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:


              ReportWriterFieldFormatting_2.png

               

              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

                • Re: SQL CONVERT function not working
                  Leon Adato

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

                    • Re: SQL CONVERT function not working
                      rgward

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

                        • Re: SQL CONVERT function not working
                          Lawrence Garvin

                          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.