Need to set field when NULL result is found 3

[glthornton]

Hi,
SQL 2000 platform, Windows 2003 server. I'm working on a query that will provide me maximum date for a specific field(MR_FINISH_DATE). The problem is that not all of my entries have data in this field and thus are returning with a NULL result. I want to know if it is possible to force a specific date/time (i.e. '1900-01-01 00:00:00.000') when a NULL result in encountered?

My query looks like this:

SELECT DISTINCT MAX(MR_FINISH_DATE) AS MAX_DATE
FROM V_LST_CPSI_ENC
WHERE MR_NUMBER = $MRN

Can I use an IF THEN ELSE statement at the end of this query to change the result?

What would be my best solution? Any and all suggestions would greatly be appreciated.

Glenn

 

[SQLSister]

use coalesce or isnull to set the null dates to something esle.

"NOTHING is more important in a database than integrity." ESquared

 

[cmartin75]

The IsNull function can help you.

ISNULL(MR_FINISH_DATE, '1900-01-01 00:00:00.000')

 

[jbenson001]

Try:

SELECT DISTINCT ISNULL(MAX(MR_FINISH_DATE), '01/01/1900') AS MAX_DATE
FROM V_LST_CPSI_ENC
WHERE MR_NUMBER = $MRN

 

[glthornton]

You folks are AWESOME!!!!

Worked like a charm! You can all go home now, you've helped a fellow user.

Cheers,
Glenn

 

[jbenson001]

LOL glad to help!