CODEWITHSUNDEEP
sqlsql-servertime
y.faz
y.faz

Reputation: 1

Difference between two dates and if one date is null use current day in SQL

I have to find difference between two contract dates. Basically between effective date and end date. But what if the contract is still on so I want the present day.(Time Stamp)? How can i combine both in SQL eg:

start d: 1/1/17 end date: 2/1/17 effective days 31
start d: 1/1/17 end date: null  effective days: total days to date.

Upvotes: 0

Views: 5544

Answers (2)

iamdave
iamdave

Reputation: 12243

You can use case expressions to compare values with each other and then use functions such as getdate() to return the current date and time.

select getdate()           -- Will return the date and time of right now

      ,case when 1 < 2
            then 'Less'
            else 'Greater'
            end            -- This will return the value 'Less'

      ,case when YourDateField < getdate()
            then YourDateField
            else getdate()
            end            --  This will return the earliest of YourDateField or right now

Instead of using the slightly verbose case when simply checking to see if a value is null or not, you can also use one of the two shorthand functions isnull and coalesce. isnull checks just the one value and coalesce returns the first not null value from a list (There are other differences, but I will leave that up to you to research):

select isnull(YourPopulatedDateField,getdate())              -- Will return YourPopulatedDateField
      ,isnull(YourNullDateField,getdate())                   -- Will return right now

      ,coalesce(YourNullDateField,getdate())                 -- Will return right now
      ,coalesce(YourNullDateField,OtherNullField, getdate()) -- Will also return right now

Combining one of these with the datediff function gives you what the other comments and answers have provided:

-- Both of these will return the same result:
select datediff(day,
               ,StartDate
               ,coalesce(EndDate, getdate())
               )

      ,datediff(day,
               ,StartDate
               ,isnull(EndDate, getdate())
               )

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT DATEDIFF( day, 
                 [start], 
                 COALESCE ([end], GETDATE())
               )

Upvotes: 3

Related Questions