How to calculate number of days by subtracting date_time field from current date_time?

Question

I have reservations table to store hotel reservations, this table has reservation_id, customer_id, rommno, and checking date also there is another optional field, checkout field which will use when person checked out and also the status field will updated to checkout status wile firstly putted checking status.

i need a query to find number of stayed dates from the checking date. needed result will look like this:

customer_id, roomno, checkin_date, due_nights

due nights will be result of subtraction of checkin_date and current date, i tried this sql query and it returns #Error

SELECT DATEDIFF(reservations.due_nights,  now(), reservations.checkin_date) from reservations

my table view is link below

current table view click

Answer 1

your query is wrong

SQL SERVER the syntax for datediff is

DATEDIFF(datepart,startdate,enddate)

Also the function for getting current date in sql server is getdate() not now()

so in your case it will be

Select DATEDIFF(DAY,reservations.checkin_date, getdate())

eg:-

select DATEDIFF(Day,'06-07-2015 14:00:00',GETDATE())
     will return 10

MS-ACCESS

DateDiff ( interval, date1, date2, [firstdayofweek], [firstweekofyear])

 DATEDIFF("d",reservations.checkin_date,Now())

  where d represents the interval as day

eg: -  DateDiff ("d", #15/10/2003#, #22/11/2003#)

       result will be 38