CODEWITHSUNDEEP
mysqlsql-server
jaya priya
jaya priya

Reputation: 75

DATEDIFF function displayed error output in sqlserver

i tried the code in mysql it is working well

 select car_id,cust_id,due,DATEDIFF(NOW(),due) as elap from rental where car_id = '1'

issue date 2019-10-16 return date 2019-10-17 have to calucalated elap result displayed in -1 it is working well.

if i tried in sqlserver it produced wrong output 

select cat_id,cust_id,date,due,DATEDIFF(dd,GETDATE(),due) as elap from rental where cat_id = '1'

have to calucalated elap result displayed in 1.wrong output right on is -1 i don't know what is the error on sqlserver code

Upvotes: 0

Views: 1472

Answers (2)

Eliseu Marcos
Eliseu Marcos

Reputation: 311

This will work correctly.

select cat_id,cust_id,date,due,DATEDIFF(dd,due,GETDATE()) as elap from rental where cat_id = '1'

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

SQL Server's DATEDIFF function uses the format:

DATEDIFF(datepart, start_date, end_date)

It returns the difference between the ending date and the starting date, in that order. So, in your example, if the due date is one day past the current date, you would get a date diff of +1.

MySQL's DATEDIFF computes the difference in the opposite order, namely taking the first date minus the second date, i.e.

DATEDIFF(date1, date2)

this would return the number of days which elapsed from date2 to get to date1, i.e. date1 - date2.

Upvotes: 2

Related Questions