Error with comparing date to null

Question

Error with comparing date to null

swin_1234

Grasshopper

Points: 11
More actions
February 26, 2010 at 2:35 pm

#219456

Hello
I am trying to assign a name "grand total" to Null value that would be at the top as a result of the query.
I get :Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
Please help
select
case when DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0) is null then 'Grand Total' else DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0) end,
tblTeams.Name as Tech_Name,
tblCustomers.Companyname as Customer,
SUM(tblJobs.Manhours) AS TotalHours, lkpJobSubStatus.Substatusname as Status,
tblSchedules.subject as Summary
from tblTeams, tblJobs, tblSchedules, tblCustomers, lkpJobSubStatus
where tblJobs.ScheduleID = tblSchedules.ScheduleID
and tblSchedules.TeamID = tblTeams.TeamID
and tblSchedules.CustomerID = tblCustomers.CustomerID
and tblJobs.SubStatusID = lkpJobSubStatus.SubStatusID
and tbljobs.substatusid = '46'
and tblSchedules.StartDate >= '1/1/2010'
and tblSchedules.StartDate <= '3/1/2010'
GROUP BY DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0), tblTeams.Name,
tblCustomers.Companyname, lkpJobSubStatus.Substatusname, tblSchedules.subject with ROLLUP
order by DATEADD(dd,DATEDIFF(dd,0,tblJobs.Startdate),0)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tim Weintz SSCommitted Points: 1622 More actions · Answer 1

Looks like it is because you are conditionally returning two different data types for the same field. Try casting the dates to varchars.

Also, there are several issues with your approach in general.

1. Consider joining outside of the where clause

2. Why use case statements instead of isnull?

3. Consider using between instead of >= <=

4. Why not alias your table names?

LutzM SSC Guru Points: 107049 More actions · Answer 2

Unfortunately, we don't know how the final date value of your case statement has to look like...

Since you're getting rid of the time portion I'm guessing you're just looking for the date value.

Therefore, I'd change the CASE statement to

CASE WHEN tblJobs.Startdate IS NULL

THEN 'Grand Total' ELSE CONVERT(CHAR(10),tblJobs.Startdate,120) END

--or following TIMs advice:

ISNULL(CONVERT(CHAR(11),@d,120),'Grand Total')

-- note: CHAR(11) due to length('Grand Total') the CASE statement is more flexible...

Please note that you'd need to change the GROUP BY and ORDER BY section as well.

Other than that I'll completely second Tim's recommendations.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 3

A better way to handle the NULLs generated by ROLLUP or CUBE is to use the GROUPING function.

An excellent example can be found in Books Online: Summarizing Data Using ROLLUP

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

Just noticed that this is the SQL Server 2008 forum, so also look at GROUPING SETS.

Books Online:

GROUPING SETS Equivalents

Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi