June 10, 2014 at 10:19 am
I cannot get the following code to execute properly. Error: Msg 241, Level 16, State 1, Line 4
Conversion failed when converting datetime from character string.
select b.model,
count(case when DATEPART(hh, u.unitcompleted) = '05' then 0 end) 'Hour_1',
count(case when DATEPART(hh, u.unitcompleted) = '06' then 0 end) 'Hour_2',
count(case when DATEPART(hh, u.unitcompleted) = '07' then 0 end) 'Hour_3',
count(case when DATEPART(hh, u.unitcompleted) = '08' then 0 end) 'Hour_4',
count(case when DATEPART(hh, u.unitcompleted) = '09' then 0 end) 'Hour_5',
count(case when DATEPART(hh, u.unitcompleted) = '10' then 0 end) 'Hour_6',
count(case when DATEPART(hh, u.unitcompleted) = '11' then 0 end) 'Hour_7',
count(case when DATEPART(hh, u.unitcompleted) = '12' then 0 end) 'Hour_8',
count(case when DATEPART(hh, u.unitcompleted) = '13' then 0 end) 'Hour_9',
count(case when DATEPART(hh, u.unitcompleted) = '14' then 0 end) 'Hour_10',
count(case when DATEPART(hh, u.unitcompleted) = '15' then 0 end) 'Hour_11',
count(case when DATEPART(hh, u.unitcompleted) = '16' then 0 end) 'Hour_12',
count(case when DATEPART(hh, u.unitcompleted) = '17' then 0 end) 'Hour_13',
count(case when DATEPART(hh, u.unitcompleted) = '18' then 0 end) 'Hour_14',
count(case when DATEPART(hh, u.unitcompleted) = '19' then 0 end) 'Hour_15',
count(case when DATEPART(hh, u.unitcompleted) = '20' then 0 end) 'Hour_16',
count(case when DATEPART(hh, u.unitcompleted) = '21' then 0 end) 'Hour_17',
count(case when DATEPART(hh, u.unitcompleted) = '22' then 0 end) 'Hour_18',
count(case when DATEPART(hh, u.unitcompleted) = '23' then 0 end) 'Hour_19',
count(case when DATEPART(hh, u.unitcompleted) = '00' then 0 end) 'Hour_20'
from completedUnit u
join assignworkorder b on u.workorder = b.workorder
where MONTH( u.dateentered ) = '6' and year(u.dateentered) = '2014'
GROUP BY b.model
ORDER BY b.model
I can get this to work:
select b.model,
count(case when DATEPART(dw, u.dateentered) = '1' then 0 end) 'SUNDAY',
count(case when DATEPART(dw, u.dateentered) = '2' then 0 end) 'MONDAY',
count(case when datepart(dw, u.dateentered) = '3' then 0 end) 'TUESDAY',
count(case when datepart(dw, u.dateentered) = '4' then 0 end) 'WEDNESDAY',
count(case when datepart(dw, u.dateentered) = '5' then 0 end) 'THURSDAY',
count(case when datepart(dw, u.dateentered) = '6' then 0 end) 'FRIDAY',
count(case when DATEPART(dw, u.dateentered) = '7' then 0 end) 'SATURDAY'
from completedUnit u
join assignworkorder b on u.workorder = b.workorder
where MONTH( u.dateentered ) = '6' and year(u.dateentered) = '2014'
GROUP BY b.model
ORDER BY b.model
June 10, 2014 at 10:29 am
You have a LOT of implicit casting going on here where you shouldn't be. DATEPART returns an integer, not a varchar. All of your comparisons are to a varchar. Change those to ints.
Next you have some nonSARGable predicates in your where clause. Instead of checking MONTH and YEAR you should check against dates. The way you have it will force a scan on that column as it has to check each and every row.
Try this instead.
from completedUnit u
join assignworkorder b on u.workorder = b.workorder
where u.dateentered >= '2014-06-01' and u.dateentered < '2014-07-01'
GROUP BY b.model
ORDER BY b.model
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 10:55 am
Thanks! I worked through it resolve
select b.model,
count(case when substring(u.unitcompleted, 1,2) = '05' then 0 end) '5AM',
count(case when substring(u.unitcompleted, 1,2) = '06' then 0 end) '6AM',
count(case when substring(u.unitcompleted, 1,2) = '07' then 0 end) '7AM',
count(case when substring(u.unitcompleted, 1,2) = '08' then 0 end) '8AM',
count(case when substring(u.unitcompleted, 1,2) = '09' then 0 end) '9AM',
count(case when substring(u.unitcompleted, 1,2) = '10' then 0 end) '10AM',
count(case when substring(u.unitcompleted, 1,2) = '11' then 0 end) '11AM',
count(case when substring(u.unitcompleted, 1,2) = '12' then 0 end) '12PM',
count(case when substring(u.unitcompleted, 1,2) = '01' then 0 end) '1PM',
count(case when substring(u.unitcompleted, 1,2) = '02' then 0 end) '2PM',
count(case when substring(u.unitcompleted, 1,2) = '03' then 0 end) '3PM',
count(case when substring(u.unitcompleted, 1,2) = '04' then 0 end) '4PM'
from completedUnit u
join assignworkorder b on u.workorder = b.workorder
where MONTH( u.dateentered ) = '6' and year(u.dateentered) = '2014'
GROUP BY b.model
ORDER BY b.model
This works to get the results. I realized the data type is varchar with time as a string and not data type timestamp.
June 10, 2014 at 11:26 am
kabaari (6/10/2014)
Thanks! I worked through it resolve
select b.model,
count(case when substring(u.unitcompleted, 1,2) = '05' then 0 end) '5AM',
count(case when substring(u.unitcompleted, 1,2) = '06' then 0 end) '6AM',
count(case when substring(u.unitcompleted, 1,2) = '07' then 0 end) '7AM',
count(case when substring(u.unitcompleted, 1,2) = '08' then 0 end) '8AM',
count(case when substring(u.unitcompleted, 1,2) = '09' then 0 end) '9AM',
count(case when substring(u.unitcompleted, 1,2) = '10' then 0 end) '10AM',
count(case when substring(u.unitcompleted, 1,2) = '11' then 0 end) '11AM',
count(case when substring(u.unitcompleted, 1,2) = '12' then 0 end) '12PM',
count(case when substring(u.unitcompleted, 1,2) = '01' then 0 end) '1PM',
count(case when substring(u.unitcompleted, 1,2) = '02' then 0 end) '2PM',
count(case when substring(u.unitcompleted, 1,2) = '03' then 0 end) '3PM',
count(case when substring(u.unitcompleted, 1,2) = '04' then 0 end) '4PM'
from completedUnit u
join assignworkorder b on u.workorder = b.workorder
where MONTH( u.dateentered ) = '6' and year(u.dateentered) = '2014'
GROUP BY b.model
ORDER BY b.model
This works to get the results. I realized the data type is varchar with time as a string and not data type timestamp.
Glad you figured it out. I would highly recommend you follow my previous suggestion about your where clause. It will help the performance of your query quite a bit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 10, 2014 at 12:39 pm
Sean Lange (6/10/2014)
I would highly recommend you follow my previous suggestion about your where clause. It will help the performance of your query quite a bit.
+1
I've seen non-SARGable predicates alone make a query take several times as long as it needs to.
June 10, 2014 at 12:58 pm
Sean,
Thanks! I use that WHERE clause for static overview type queries. I will definitely take that into consideration.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply