How do I use where ISDATE(FirstDateCol) = 1 when the field is empty

When you say that it is empty, do you mean NULL or empty string?

Greg

empty string
and thanks for the great help yesterday

Do you have a default date you want for either field if empty string, or do you want result to say NULL or 0 for that row?

You can trim the spaces off of the data in the fields,  you can specifically test for the varchar(10) being spaces, or you can replace each of the spaces with '0'  before the test.

SELECT DATEDIFF(dd, FirstDate, SecondDate)
WHERE  ISDATE(LTRIM(FirstDateCol)) = 1 
  AND  ISDATE(LTRIM(SecondDateCol)) = 1;
 
or
 
SELECT DATEDIFF(dd, FirstDate, SecondDate)
WHERE  FirstDatecol <> '          '
  AND  SecondDateCol <> '          '
  AND  ISDATE(FirstDateCol) = 1 
  AND  ISDATE(SecondDateCol) = 1;
 
or 
SELECT DATEDIFF(dd, FirstDate, SecondDate)
WHERE  ISDATE(REPLACE(FirstDateCol, ' ', '0')) = 1 
  AND  ISDATE(REPLACE(SecondDateCol, ' ', '0')) = 1;

Select allOpen in new window

Well the field is used for the SQL
SELECT DATEDIFF(dd, FirstDate, SecondDate)
WHERE ISDATE(FirstDateCol) = 1 AND ISDATE(SecondDateCol) = 1

What do you think is best because I don't know?

If can default, then can try something like this.
If first date is empty, then use date 0.
If second date is empty, then use today.

SELECT DATEDIFF(dd
		, CASE ISDATE(FirstDateCol) WHEN 1 THEN CAST(FirstDateCol AS DATETIME) ELSE 0 END
		, CASE ISDATE(SecondDateCol) WHEN 1 THEN CAST(SecondDateCol AS DATETIME) ELSE getdate() END)
WHERE ISDATE(FirstDateCol) = 1 OR ISDATE(SecondDateCol) = 1

Select allOpen in new window

You're welcome.  I'm glad that I was able to help.

Even ISDATE(''), should resolve to 0 or false.  It shouldn't error out.  You could add FirstDateCol <> '' AND SecondDateCol <> '', but I suspect that something else is going on.  If that also fails, check and see how many records are returned.  Then run a query like SELECT TOP (number of records + 1) * FROM Table1.  The last record should contain the record causing the error.

Greg

I agree with JestersGrind which is why I made my suggestion.

I am figuring it is appearing not to work because you are missing records where one of the columns is NOT a valid date which is why I changed to OR in the where clause.  Then you just have to use CASE logic in the DATEDIFF to set the defaults you want OR do something like this:
+Returns a record as long as one of the dates is valid
+Returns the date difference in days only if both columns are dates
+Returns NULL if one of the dates are missing

SELECT CASE WHEN ISDATE(FirstDateCol) = 1 AND ISDATE(SecondDateCol) = 1
THEN DATEDIFF(dd, FirstDateCol, SecondDateCol) END
FROM TableName
WHERE ISDATE(FirstDateCol) = 1 OR ISDATE(SecondDateCol) = 1

Select allOpen in new window

OK The row it is failing on has the word False in the date column and at that row the qhery fails with
Syntax error converting datetime from character string
Which of the suggestions is best for this conrition also?

Would think any of the CASE options I showed should work; however, not sure why you got that error in the first place if you were using a WHERE clause that said both columns had to be dates.  Check that you had the correct column names in function to match what was in the WHERE.

But again, SELECT ISDATE('FALSE') equates to 0, so this should work http:#23871161.

@expertsexchangehunter,
Can you paste into the CODE field the query that you are trying to execute at this point?

I updated all the date fields with False to ''
and it reads most of the records then reports
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The record it gets this on has dates in 4 of the 5 date fields and this is not the onlly record missing a date field. I will investigate and reply back.

OK. The row it stoped on had a date field of 1/2/0001 and 1/2/0001 causing the overflow
After I corrected it the query ran through.
I used on all the date fields
WHERE  ISDATE(REPLACE(FirstDateCol, ' ', '0')) = 1
  AND  ISDATE(REPLACE(SecondDateCol, ' ', '0')) = 1
I am getting large negative numbers like -38494 on fields resulting from
DATEDIFF(dd, FirstDate, SecondDate)
These are comming where most of the date fileds are empty.
How can I get them to be a correct number?
Say the first field is a good date and the rest are empty

More info.
Fields with good dates produce good numbers and if there is a empty date in the datediff it produces
the large negative number. Maybe calculating from day 0.

If the first field is a valid date and the second one is empty, how do you define "a correct number"?  
One option would be to, in effect, pretend that the second field has toady's date in it and then calculate the difference between the FirstDate and Today's date.
Again, please check the "Attach Code Snippet" box and then cut and paste the query into the section that appears.  Without seeing the whole of your query, it is hard to figure out what may be causing the issue.

here is a partial select statement

SELECT dbDVUser.TestRequest.ControlNumber as [Control Number],
DateDiff(dd, Convert(DateTime, DateSubmitted), Convert(DateTime, DateCompleted)) as [Total], 
DateDiff(dd, Convert(DateTime, DateScheduled), Convert(DateTime, DateCancelled)) as [Cancelled], 
DateDiff(dd, Convert(DateTime, DateStarted), Convert(DateTime, DateCompleted)) as [Active], 
DateDiff(dd, Convert(DateTime, DateSubmitted), Convert(DateTime, DateStarted)) as [New]
 
FROM dbDVUser.TestRequest 
WHERE 
 
or (ISDATE(Replace(dbDVUser.TestRequest.DateCompleted,'',0)) = 1) 
or (ISDATE(replace(dbDVUser.TestRequest.DateSubmitted,'','0'))  = 1) 
or (ISDATE(replace(dbDVUser.TestRequest.DateStarted,'','0')) = 1)
or (ISDATE(Replace(dbDVUser.TestRequest.DateCancelled,'','0'))  = 1) 

Select allOpen in new window

Try just like this:

SELECT dbDVUser.TestRequest.ControlNumber as [Control Number]
, CASE WHEN ISDATE(DateSubmitted) = 1 AND ISDATE(DateCompleted) = 1 THEN DateDiff(dd, DateSubmitted, DateCompleted) END as [Total]
, CASE WHEN ISDATE(DateScheduled) = 1 AND ISDATE(DateCancelled) = 1 THEN DateDiff(dd, DateScheduled, DateCancelled) END as [Cancelled]
, CASE WHEN ISDATE(DateStarted) = 1 AND ISDATE(DateCompleted) = 1 THEN DateDiff(dd, DateStarted, DateCompleted) END as [Active]
, CASE WHEN ISDATE(DateSubmitted) = 1 AND ISDATE(DateStarted) = 1 THEN DateDiff(dd, DateSubmitted, DateStarted) END as [New]
FROM dbDVUser.TestRequest 

Select allOpen in new window

When an empty string gets converted to a datatime, it become 1/1/1900.  Which is probably not what you want.  Also, the 1/2/0001 is beyond the scope of the datatime datatype, so you have a data integrity issue.  You can find these bad dates with this:

SELECT *
FROM #Table1 AS t
WHERE CONVERT(INT, RIGHT(DateCol, 4)) < 1900

Then you can fix them or include CONVERT(INT, RIGHT(DateCol, 4)) > 1900 in your WHERE clause.  

Greg

That would skip the row  and say the four date filrds are
Submitted Completed Started Cancelled and even if one
date is missing I can still get valid numbers uding the other
dates in that row.
I am trying the CASE statement from above
SELECT DATEDIFF(dd
            , CASE ISDATE(FirstDateCol) WHEN 1 THEN CAST(FirstDateCol AS DATETIME) ELSE 0 END
and replacing the missing date with the submitted date instead of 0

Okay, thank you . . . that gives some more insight. ;-)
I think that the problem may be that ou are forcing the dates to have all zeros if they are all spaces in the WHERE clause but NOT in the main body.  You need to use the REPLACE() function in the main body.  
In calculating the TOTAL time, I would think that your would want to use
DATEDIFF(dd, CONVERT(DATETIME, DateCompleted), CONVERT(DATETIME, DateSubmitted)
Similarly, you need to make sure that you are setting up the other dates in the correct order.  THe second date value will be subtracted from the first one, not the other way around.
I would also set up a subselect that pulled the data in a manner that is valid before trying to do the calculations.
I would also add a check to make sure that the first date in the DATEDIFF functions is greater than or equal to the second date.  
Something like the code below:
 

SELECT ControlNumber
				CASE WHEN DateCompleted > DateSubmitted
				     THEN DateDiff(dd, DateCompleted, DateSubmitted)
				     ELSE 0 
				END AS [Total], 
				CASE WHEN DateCancelled > DateScheduled 
				     THEN DateDiff(dd, DateCancelled, DateScheduled)
				     ELSE 0 
				END AS [Cancelled]
				CASE WHEN DateCompleted > DateStarted
				     THEN DateDiff(dd, DateCompleted, DateStarted)
				     ELSE 0 
				END AS [Active],
				CASE WHEN DateStarted> DateSubmitted
				     THEN DateDiff(dd, DateStarted, DateSubmitted)
				     ELSE 0 
				END AS [New] 
FROM
(    
 SELECT ControlNumber,
        CASE WHEN ISDATE(DateSubmitted) = 1 
             THEN Convert(DateTime, DateSubmitted)
             ELSE GETDATE()
        END AS DateSubmitted,
        CASE WHEN ISDATE(DateStarted) = 1 
             THEN Convert(DateTime, DateStarted)
             ELSE GETDATE()
        END AS DateStarted,
        CASE WHEN ISDATE(DateCompleted) = 1 
             THEN Convert(DateTime, DateCompleted)
             ELSE GETDATE()
        END AS DateCompleted,
        CASE WHEN ISDATE(DateCancelled) = 1 
             THEN Convert(DateTime, DateCancelled)
             ELSE GETDATE()
        END AS DateCancelled
 FROM   dbDVUser.TestRequest
) Z

Select allOpen in new window

OK Give me some time to add and test this.
Instead if GETDATE using datesubmitted might be better so it gives a result of 0 days

If DateSubmitted is valid, that might be the better choice.  That's one of the nice things about using the subselect/subquery approach . . . it's usually a bit easier to fiddle the defaults you are setting. ;-)

I added
 SELECT ControlNumber
                        CASE WHEN DateCompleted > DateSubmitted
                             THEN DateDiff(dd, DateCompleted, DateSubmitted)
                             ELSE 0
                        END AS [Total],
                        CASE WHEN DateCancelled > DateScheduled
                             THEN DateDiff(dd, DateCancelled, DateScheduled)
                             ELSE 0
                        END AS [Cancelled]
                        CASE WHEN DateCompleted > DateStarted
                             THEN DateDiff(dd, DateCompleted, DateStarted)
                             ELSE 0
                        END AS [Active],
                        CASE WHEN DateStarted> DateSubmitted
                             THEN DateDiff(dd, DateStarted, DateSubmitted)
                             ELSE 0
                        END AS [New]
FROM
(
SELECT dbDVUser.TestRequest.ControlNumber as [Control Number],
Dat

and am getting
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CASE'.
Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'ORDER'.

Why is it getting this since it looks correct?

I removed the Select above the case and the error went away
Now it complains about the
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Sorry about that, my bad!
I left a comma off of the end of the first line.
I'm not sure what the reference is to the "ORDER BY", though, unless you added something to the query.

SELECT ControlNumber,
       CASE WHEN DateCompleted > DateSubmitted
            THEN DateDiff(dd, DateCompleted, DateSubmitted)
            ELSE 0 
       END AS [Total], 
       CASE WHEN DateCancelled > DateScheduled 
            THEN DateDiff(dd, DateCancelled, DateScheduled)
            ELSE 0 
       END AS [Cancelled]
       CASE WHEN DateCompleted > DateStarted
            THEN DateDiff(dd, DateCompleted, DateStarted)
            ELSE 0 
       END AS [Active],
       CASE WHEN DateStarted> DateSubmitted
            THEN DateDiff(dd, DateStarted, DateSubmitted)
            ELSE 0 
       END AS [New] 
FROM
(    
 SELECT ControlNumber,
        CASE WHEN ISDATE(DateSubmitted) = 1 
             THEN Convert(DateTime, DateSubmitted)
             ELSE GETDATE()
        END AS DateSubmitted,
        CASE WHEN ISDATE(DateStarted) = 1 
             THEN Convert(DateTime, DateStarted)
             ELSE GETDATE()
        END AS DateStarted,
        CASE WHEN ISDATE(DateCompleted) = 1 
             THEN Convert(DateTime, DateCompleted)
             ELSE GETDATE()
        END AS DateCompleted,
        CASE WHEN ISDATE(DateCancelled) = 1 
             THEN Convert(DateTime, DateCancelled)
             ELSE GETDATE()
        END AS DateCancelled
 FROM   dbDVUser.TestRequest
) Z 

Select allOpen in new window

I added the comma and changed it to
 Select
                        CASE WHEN DateCompleted > DateSubmitted
                             THEN DateDiff(dd, DateCompleted, DateSubmitted)
                             ELSE 0
                        END AS [Total],
and now I get an syntax error on the ) at the end of the entire  statement
Line 42: Incorrect syntax near ')'.
GROUP BY dbDVuser.TestRequest.ControlNumber, dbDVuser.TestRequest.Description, dbo.SeriesModel.SMDescription,
dbDVuser.TestRequest.bitFinalCheckOff, ProductCategory.Description, ProductCategory.ProductCategoryID,
dbDVuser.TestRequest.DateScheduled, dbDVuser.TestRequest.DateCompleted, dbDVuser.TestRequest.DateSubmitted,
dbDVuser.TestRequest.DateCancelled, dbDVuser.TestRequest.DateStarted ORDER BY dbDVuser.TestRequest.ControlNumber)

It still does not work. If you want the statement let me know.

The entire statement would be helpful.
However, when you say that you added the comm, did you add the ControlNumber back into the first line of the SELECT as well?

Oh, yeah, did you C&P the whole of the last version of the query I supplied and try that?

Heres the query

 Select 
				CASE WHEN DateCompleted > DateSubmitted
				     THEN DateDiff(dd, DateCompleted, DateSubmitted)
				     ELSE 0 
				END AS [Total], 
				CASE WHEN DateCancelled > DateScheduled 
				     THEN DateDiff(dd, DateCancelled, DateScheduled)
				     ELSE 0 
				END AS [Cancelled],
				CASE WHEN DateCompleted > DateStarted
				     THEN DateDiff(dd, DateCompleted, DateStarted)
				     ELSE 0 
				END AS [Active],
				CASE WHEN DateStarted> DateSubmitted
				     THEN DateDiff(dd, DateStarted, DateSubmitted)
				     ELSE 0		
				END AS [New] 
FROM
( 
 SELECT ControlNumber,
        CASE WHEN ISDATE(DateSubmitted) = 1 
             THEN Convert(DateTime, DateSubmitted)
             ELSE GETDATE()
        END AS DateSubmitted,
        CASE WHEN ISDATE(DateStarted) = 1 
             THEN Convert(DateTime, DateStarted)
             ELSE GETDATE()
        END AS DateStarted,
        CASE WHEN ISDATE(DateCompleted) = 1 
             THEN Convert(DateTime, DateCompleted)
             ELSE GETDATE()
        END AS DateCompleted,
        CASE WHEN ISDATE(DateCancelled) = 1 
             THEN Convert(DateTime, DateCancelled)
             ELSE GETDATE()
        END AS DateCancelled
 FROM   dbDVUser.TestRequest
) 
SELECT top 100 percent dbDVUser.TestRequest.ControlNumber as [Control Number],
DateDiff(dd, Convert(DateTime, DateSubmitted), Convert(DateTime, DateCompleted)) as [Total], 
DateDiff(dd, Convert(DateTime, DateScheduled), Convert(DateTime, DateCancelled)) as [Cancelled], 
DateDiff(dd, Convert(DateTime, DateStarted), Convert(DateTime, DateCompleted)) as [Active], 
DateDiff(dd, Convert(DateTime, DateSubmitted), Convert(DateTime, DateStarted)) as [New]
FROM dbDVUser.TestRequest 
INNER JOIN dbdvuser.TestType ON dbdvuser.TestRequest.TestType = dbdvuser.TestType.TestTypeID 
INNER JOIN dbdvuser.TestRqstStatus ON dbdvuser.TestRequest.TRStatus = dbdvuser.TestRqstStatus.TRStatusID 
INNER JOIN dbo.SMSearchID ON dbdvuser.TestRequest.SMSearchID = dbo.SMSearchID.SMSearchID 
INNER JOIN dbo.SeriesModel ON dbdvuser.TestRequest.SeriesModel = dbo.SeriesModel.SeriesModelID 
INNER JOIN dbo.ProductCategory ON dbo.SeriesModel.ProductCategoryID = dbo.ProductCategory.ProductCategoryID 
WHERE (charindex('|' + cast(dbDVuser.TestRequest.bitFinalCheckOff as nvarchar(30)) + '|','|0|,|1|,|2|') > 0) 
and (charindex('|' + cast(dbdvuser.TestRqstStatus.TRStatusID as nvarchar(30)) + '|','|1|,|2|,|0|,|6|,|7|,|8|,|9|') > 0 )  AND (ISDATE(dbDVUser.TestRequest.DateScheduled)  = 1) 
AND (dbDVUser.TestRequest.SMSearchID = 59) and (dbo.SeriesModel.blnActive = 1) 
AND (charindex('|' + cast(dbDVuser.TestRequest.intRequestType as nvarchar(50)) + '|','|1|,|0|,|2|') > 0 ) 
AND (dbDVuser.TestRequest.controlNumber =  '4341')
or (ISDATE(Replace(dbDVUser.TestRequest.DateCompleted,'',0)) = 1) 
or (ISDATE(replace(dbDVUser.TestRequest.DateSubmitted,'','0'))  = 1) 
or (ISDATE(replace(dbDVUser.TestRequest.DateStarted,'','0')) = 1)
or (ISDATE(Replace(dbDVUser.TestRequest.DateCancelled,'','0'))  = 1) 
GROUP BY dbDVuser.TestRequest.ControlNumber, dbDVuser.TestRequest.Description, dbo.SeriesModel.SMDescription, 
dbDVuser.TestRequest.bitFinalCheckOff, ProductCategory.Description, ProductCategory.ProductCategoryID, 
dbDVuser.TestRequest.DateScheduled, dbDVuser.TestRequest.DateCompleted, dbDVuser.TestRequest.DateSubmitted, 
dbDVuser.TestRequest.DateCancelled, dbDVuser.TestRequest.DateStarted ORDER BY dbDVuser.TestRequest.ControlNumber

Select allOpen in new window

That loks like you have jammed all of the offerings together, pretty much willy-nilly, and then tried to execute it.
Cut and past the following code (and ONLY the following code) and see if it works.

SELECT ControlNumber,
       CASE WHEN DateCompleted > DateSubmitted
            THEN DateDiff(dd, DateCompleted, DateSubmitted)
            ELSE 0 
       END AS [Total], 
       CASE WHEN DateCancelled > DateScheduled 
            THEN DateDiff(dd, DateCancelled, DateScheduled)
            ELSE 0 
       END AS [Cancelled]
       CASE WHEN DateCompleted > DateStarted
            THEN DateDiff(dd, DateCompleted, DateStarted)
            ELSE 0 
       END AS [Active],
       CASE WHEN DateStarted> DateSubmitted
            THEN DateDiff(dd, DateStarted, DateSubmitted)
            ELSE 0 
       END AS [New] 
FROM
(    
 SELECT ControlNumber,
        CASE WHEN ISDATE(DateSubmitted) = 1 
             THEN Convert(DateTime, DateSubmitted)
             ELSE GETDATE()
        END AS DateSubmitted,
        CASE WHEN ISDATE(DateStarted) = 1 
             THEN Convert(DateTime, DateStarted)
             ELSE GETDATE()
        END AS DateStarted,
        CASE WHEN ISDATE(DateCompleted) = 1 
             THEN Convert(DateTime, DateCompleted)
             ELSE GETDATE()
        END AS DateCompleted,
        CASE WHEN ISDATE(DateCancelled) = 1 
             THEN Convert(DateTime, DateCancelled)
             ELSE GETDATE()
        END AS DateCancelled
 FROM   dbDVUser.TestRequest
) Z 

Select allOpen in new window

I added a comma after END AS [Cancelled]
and am etting

Msg 207, Level 16, State 3, Line 1
Invalid column name 'DateScheduled'.
Msg 207, Level 16, State 3, Line 1
Invalid column name 'DateScheduled'.
and 'DateScheduled' is a valid colmn

Did you try doing the cut and paste like I recommended?
There is no need for a comma at that point and, as you have seen, it does in fact invalidate the SQL.
You need a comma after all except the last column specified in the SELECT clause.  
I missed the DateScheduled in the subselect, sorry.  See below.  
Please note, cut this code and use it to replace the previous code in whatever you are using to test the code.  THis is not to be added to any code but is to replace it.

SELECT ControlNumber,
       CASE WHEN DateCompleted > DateSubmitted
            THEN DateDiff(dd, DateCompleted, DateSubmitted)
            ELSE 0 
       END AS [Total], 
       CASE WHEN DateCancelled > DateScheduled 
            THEN DateDiff(dd, DateCancelled, DateScheduled)
            ELSE 0 
       END AS [Cancelled]
       CASE WHEN DateCompleted > DateStarted
            THEN DateDiff(dd, DateCompleted, DateStarted)
            ELSE 0 
       END AS [Active],
       CASE WHEN DateStarted> DateSubmitted
            THEN DateDiff(dd, DateStarted, DateSubmitted)
            ELSE 0 
       END AS [New] 
FROM
(    
 SELECT ControlNumber,
        CASE WHEN ISDATE(DateSubmitted) = 1 
             THEN Convert(DateTime, DateSubmitted)
             ELSE GETDATE()
        END AS DateSubmitted,
        CASE WHEN ISDATE(DateScheduled) = 1 
             THEN Convert(DateTime, DateScheduled)
             ELSE GETDATE()
        END AS DateScheduled,
        CASE WHEN ISDATE(DateStarted) = 1 
             THEN Convert(DateTime, DateStarted)
             ELSE GETDATE()
        END AS DateStarted,
        CASE WHEN ISDATE(DateCompleted) = 1 
             THEN Convert(DateTime, DateCompleted)
             ELSE GETDATE()
        END AS DateCompleted,
        CASE WHEN ISDATE(DateCancelled) = 1 
             THEN Convert(DateTime, DateCancelled)
             ELSE GETDATE()
        END AS DateCancelled
 FROM   dbDVUser.TestRequest
) Z 

Select allOpen in new window

OK. I copied and pastied it and got
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'CASE'.
Msg 170, Level 15, State 1, Line 42
Line 42: Incorrect syntax near 'Z'.
I added the comma and it runs.
I need to validate the output.

8080 Diver:
Thank you for staying with this.
I moved the fields for the DataDiff functions to their correct positions
and the numbers look OK except when they are missing the date because
some of the scheduling ships steps and some of the dates are missing.
I see that when the field is not a date the current date is used. I'm wondering if
using dateSubmitted is better since it's always the earliest date and is never missing.

If dateSubmitted is always going to be there, then I would use that one.  The use of GetDate() was, in effect, a place holder since I don'tknow the Business Rules where you are.
Sounds like you have this one under control .;-)

Thank you for your help with this and I know I didn't make it any easier. The result was great. Thank you.

One more thing is there are other selection criteria with the above sql
I know that after the sql there is a Z
I have an existing select with
select contnumb
from dbdvuser.testrequest
inner join ...
where ...
group by
order by
I added the select and get an error on the select
What do I have to do to add the additional select statement?

I am not sure what you mean by "add an additional select statement."  
If you are trying to add some additional columns from another table to the query that is now working, then that is one thing.  If you are trying to put this in a stored procedure and have a separate SELECT of data, that is another.
 

The original query selected one column and has several
inner joins, a where clause a group by and a norder by

Yes, that is a fair summary of the original query; although, if you look closely, the inner select statement actually pulls several columns from the original table (it just manipulates them a bit ;-).
Are you trying to select additional columns from dbDVUser.TestRequest?
If so, then you need to add that column name into the list of column names selected by both the inner select and the outer select (either at the end of the list and preceeded by a comma or anywhere else in the list and followed by a comma).
Once you are selecting things from a table, you can generally just add more column names to the list of columns being selected.

 

The column ControlNumber is the only column
so do I add the
FROM dbDVUser.TestRequest
INNER JOIN dbdvuser.TestType ON dbdvuser.TestRequest.TestType = dbdvuser.TestType.TestTypeID
INNER JOIN dbdvuser.TestRqstStatus ON dbdvuser.TestRequest.TRStatus = dbdvuser.TestRqstStatus.TRStatusID
where the Z is?

I tried thee following and it worked. I guess rither way works. Please what is your opinion on this?

        END AS DateCancelled
 FROM dbDVUser.TestRequest
INNER JOIN ....
....
) Z

Without quite a bit more than the tiny snippet you posted, I have no idea what you tried.  However, if it worked, then it worked. ;-)

OK Then this is what I used. The SQL inner joins and where clauses before the ) Z

SELECT ControlNumber,
       CASE WHEN DateCompleted > DateSubmitted
            THEN DateDiff(dd, DateCompleted, DateSubmitted)
            ELSE 0
       END AS [Total],
       CASE WHEN DateCancelled > DateScheduled
            THEN DateDiff(dd, DateCancelled, DateScheduled)
            ELSE 0
       END AS [Cancelled]
       CASE WHEN DateCompleted > DateStarted
            THEN DateDiff(dd, DateCompleted, DateStarted)
            ELSE 0
       END AS [Active],
       CASE WHEN DateStarted> DateSubmitted
            THEN DateDiff(dd, DateStarted, DateSubmitted)
            ELSE 0
       END AS [New],
       S.WhateverColumn
FROM
(    
 SELECT ControlNumber,
        CASE WHEN ISDATE(DateSubmitted) = 1
             THEN Convert(DateTime, DateSubmitted)
             ELSE GETDATE()
        END AS DateSubmitted,
        CASE WHEN ISDATE(DateScheduled) = 1
             THEN Convert(DateTime, DateScheduled)
             ELSE GETDATE()
        END AS DateScheduled,
        CASE WHEN ISDATE(DateStarted) = 1
             THEN Convert(DateTime, DateStarted)
             ELSE GETDATE()
        END AS DateStarted,
        CASE WHEN ISDATE(DateCompleted) = 1
             THEN Convert(DateTime, DateCompleted)
             ELSE GETDATE()
        END AS DateCompleted,
        CASE WHEN ISDATE(DateCancelled) = 1
             THEN Convert(DateTime, DateCancelled)
             ELSE GETDATE()
        END AS DateCancelled
 FROM   dbDVUser.TestRequest
INNER JOIN SomeOTherTable S
   ON S.ControlNumber = Z.ControlNumber
WHERE (whatever constraints you need)
) Z
 

Thank you for your help with this and I know I didn't make it any easier. The result was great. Thank you