[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Character conversion failed

This query is doing 99% of what I want but group by date. When I try to format the date to the way I wan it grouped it throws the error below. In the case statement below, I removed the code LEFT(d.Thedate,7) in order to return results. If I were to add that code back to this query I get the same error below. I'm stuck!

Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.


DECLARE @ReportMode INT,
        @startDate  DATETIME,
        @endDate    DATETIME,
        @agencyID   VARCHAR(100),
        @divisionID VARCHAR(10);

SET @ReportMode = 2;
SET @startDate = '2009-01-01';
SET @endDate = '2009-12-31';
SET @divisionID = '5';
SET @agencyID = '0';

SELECT a.[id]       AS agency_id,
       a.agency_name,
       COUNT(ti.id) AS tran_total,
       reportdate
FROM   (SELECT a.[ID],
               a.agency_name,
               CASE
                 WHEN @ReportMode  = 3 THEN LEFT(d.thedate, 4)
                 WHEN @ReportMode >= 2 d.thedate
                 WHEN @ReportMode >= 1 THEN LEFT(d.thedate, 9)
               END AS reportdate
        FROM   agencies AS a,
               (SELECT d AS thedate
                FROM   (SELECT DISTINCT tidate.dreceived AS d
                        FROM   transactions_incoming tidate
                        WHERE  tidate.dreceived BETWEEN @startDate AND @endDate) AS x) AS d
        WHERE  a.[id] IN ( CASE
                             WHEN @agencyID = '0' THEN a.[ID]
                             ELSE (SELECT orderid
                                   FROM   Splitorderids(@agencyID) AS agencylist)
                           END )
               AND a.[agency_division_id] IN ( CASE
                                                 WHEN @divisionID = '0' THEN a.[agency_division_id]
                                                 ELSE (SELECT orderid
                                                       FROM   Splitorderids(@divisionID) AS divisionlist)
                                               END )) AS a
       LEFT OUTER JOIN (SELECT *
                        FROM   transactions_incoming) AS ti
         ON a.id = ti.agency_id
            AND ti.dreceived = a.reportdate
GROUP  BY a.id,
          a.agency_name,
          reportdate
ORDER  BY reportdate,
          a.id;

Open in new window

0
jgoodale
Asked:
jgoodale
  • 6
  • 6
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
Try change

LEFT(d.Thedate,7)

to

LEFT(LTRIM(CAST(d.Thedate AS VARCHAR(20))),7)

Raj
0
 
jgoodaleAuthor Commented:
Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.
0
 
Rajkumar GsSoftware EngineerCommented:
What result you meant to display by LEFT(d.Thedate,7) ?

Raj
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rajkumar GsSoftware EngineerCommented:

Since I am from mobile, can't read code-snipplet fully

Raj
0
 
jgoodaleAuthor Commented:
Same result

Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.

Here is an example of the dataset that returns of no formatting is used on the column

55      Downey PD      0      2009-01-01 00:00:00.000
55      Downey PD      18      2009-01-02 00:00:00.000
55      Downey PD      0      2009-01-03 00:00:00.000
55      Downey PD      0      2009-01-04 00:00:00.000
0
 
jgoodaleAuthor Commented:
DECLARE @ReportMode INT,
        @startDate  DATETIME,
        @endDate    DATETIME,
        @agencyID   VARCHAR(100),
        @divisionID VARCHAR(10);

SET @ReportMode = 2;
SET @startDate = '2009-01-01';
SET @endDate = '2009-12-31';
SET @divisionID = '5';
SET @agencyID = '0';

SELECT a.[id]       AS agency_id,
       a.agency_name,
       COUNT(ti.id) AS tran_total,
       reportdate
FROM   (SELECT a.[ID],
               a.agency_name,
               CASE
                 WHEN @ReportMode  = 3 THEN LEFT(d.thedate, 4)
                 WHEN @ReportMode >= 2 d.thedate
                 WHEN @ReportMode >= 1 THEN LEFT(d.thedate, 9)
               END AS reportdate
        FROM   agencies AS a,
               (SELECT d AS thedate
                FROM   (SELECT DISTINCT tidate.dreceived AS d
                        FROM   transactions_incoming tidate
                        WHERE  tidate.dreceived BETWEEN @startDate AND @endDate) AS x) AS d
        WHERE  a.[id] IN ( CASE
                             WHEN @agencyID = '0' THEN a.[ID]
                             ELSE (SELECT orderid
                                   FROM   Splitorderids(@agencyID) AS agencylist)
                           END )
               AND a.[agency_division_id] IN ( CASE
                                                 WHEN @divisionID = '0' THEN a.[agency_division_id]
                                                 ELSE (SELECT orderid
                                                       FROM   Splitorderids(@divisionID) AS divisionlist)
                                               END )) AS a
       LEFT OUTER JOIN (SELECT *
                        FROM   transactions_incoming) AS ti
         ON a.id = ti.agency_id
            AND ti.dreceived = a.reportdate
GROUP  BY a.id,
          a.agency_name,
          reportdate
ORDER  BY reportdate,
          a.id;
0
 
Rajkumar GsSoftware EngineerCommented:
About

  ...CASE
                 WHEN @ReportMode  = 3 THEN LEFT(d.thedate, 4)
                 WHEN @ReportMode >= 2 d.thedate
                 WHEN @ReportMode >= 1 THEN LEFT(d.thedate, 9)
               END AS reportdate...

It's logic ?

If you want to format date, removing time part , try

CONVERT(VARCHAR(10), urDateField, 102)

Raj
0
 
jgoodaleAuthor Commented:
Still getting a conversion error when I changed the code to read:

 WHEN @ReportMode >= 2 THEN Left(CONVERT(VARCHAR(10), d.thedate, 102),7)
0
 
Rajkumar GsSoftware EngineerCommented:
>> When I try to format the date to the way I wan it grouped it throws the error below.

Let me know what format you want. I think it would be better so that I can correct it.
I mean by eg:- like 01/Jan/2010 or something like that

As per your query, for each reportmode, you need different formats, right ? Tell me the format by example for each mode.

Raj
0
 
jgoodaleAuthor Commented:
there are 3 formats, Day, Month and Year:

2010-04-01
2010-04
2010
0
 
Rajkumar GsSoftware EngineerCommented:
Instead of LEFT function, use this in the query

2010-04-01  --> CONVERT(VARCHAR(10), d.thedate, 102)
2010-04  --> CONVERT(VARCHAR(7), d.thedate, 102)
2010    --> YEAR(d.thedate)

Hope this helps
Raj
0
 
jgoodaleAuthor Commented:
I ended up having to use a different solution since I couldn't avoid the error. Thanks for your help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now