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.
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;
ASKER
Msg 241, Level 16, State 1, Line 13
Conversion failed when converting date and/or time from character string.
Conversion failed when converting date and/or time from character string.
What result you meant to display by LEFT(d.Thedate,7) ?
Raj
Raj
Since I am from mobile, can't read code-snipplet fully
Raj
ASKER
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
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
ASKER
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)
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;
@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)
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;
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
...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
ASKER
Still getting a conversion error when I changed the code to read:
WHEN @ReportMode >= 2 THEN Left(CONVERT(VARCHAR(10), d.thedate, 102),7)
WHEN @ReportMode >= 2 THEN Left(CONVERT(VARCHAR(10), d.thedate, 102),7)
>> 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
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
ASKER
there are 3 formats, Day, Month and Year:
2010-04-01
2010-04
2010
2010-04-01
2010-04
2010
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ended up having to use a different solution since I couldn't avoid the error. Thanks for your help.
LEFT(d.Thedate,7)
to
LEFT(LTRIM(CAST(d.Thedate AS VARCHAR(20))),7)
Raj