How do you pull Max Date when Date is Null in SQL query

Question

I am working on a SQL query to pull the minimum begin and maximum end date for recurring classes. The way the database is set up, If the classes are not continuing they have an end date, if the classes are continuing then the end date is null. The field is Data Type datetime.

If the end date is null that is essentially the date I want because it is the maximum end date but I cannot seem to work out how to get that to work? I get whatever the last actual date in that field is and cannot get the query to return either null or better yet a default date that I set.

Thanks in advance

Edit your question and provide sample data and desired results. — Gordon Linoff, Jul 02 '18 at 19:50
Use the ISNULL function and return the default date where that datetime is NULL or use Gordon's answer below. — Ryan Wilson, Jul 02 '18 at 19:52
`MAX(ISNULL(date, '2050-01-01'))` ... this returns 2050 if there are null values. — Salman A, Jul 02 '18 at 19:53

score 3 · Answer 1 · answered Jul 02 '18 at 19:52

3

If you want the null date as the max, you can do so with a case expression:

select (case when count(*) = count(enddate) then max(enddate) end)

The count(*) = count(enddate) basically says that none of the enddate values are NULL.

If you like playing around with functions, you can also use:

select nullif(max(coalesce(enddate, '9999-01-01')), '9999-01-01')

But I prefer the first method.

answered Jul 02 '18 at 19:52

Gordon Linoff

1,242,037
58
646
786

Thank you that worked. I have been looking for answers for days. – daisy2011 Jul 02 '18 at 20:39

How do you pull Max Date when Date is Null in SQL query

1 Answers1