CODEWITHSUNDEEP
sqlif-statementgetdate
LewisFletch
LewisFletch

Reputation: 125

How to implement ISNULL and GETDATE into DATEDIFF statement

"the column should use ISNULL and GETDATE to calculate the current rental duration if the return date is NULL. (i.e. if there is no return date, use the current date.)" Currently this is the snippet of code i have so far completed, i need to create a ISNULL and GETDATE statement for use with rental duration. i am not sure if i need to use an IF statement or where to put said statement in my current configuration.

SELECT mo.Movie_ID                     
 , co.copy_id                      
 , mo.Movie_Name
 , fo.format_name                  
 , c.customer_id
 , rental_ID
 , DATEDIFF (day, rental_date, return_date) AS rental_duration
 , c.first_name + ' ' + c.last_name AS customer_name

Thanks in advance. any help would be appreciated.

Upvotes: 1

Views: 11332

Answers (2)

Jim H.
Jim H.

Reputation: 5579

ISNULL is an inline statement which, when the source column is not null, returns that column and when it is null, returns an alternate value.

SELECT ISNULL(return_date, getdate()) ...

When return_date is null, the function getdate() is called to return the proper value.

Upvotes: 0

Icarus
Icarus

Reputation: 63966

SELECT mo.Movie_ID                     
 , co.copy_id                      
 , mo.Movie_Name
 , fo.format_name                  
 , c.customer_id
 , rental_ID
 , DATEDIFF (day, rental_date, ISNULL(return_date,GETDATE())) AS rental_duration
 , c.first_name + ' ' + c.last_name AS customer_name

That should do it.

Upvotes: 2

Related Questions