CODEWITHSUNDEEP
sqldatabasems-accessms-access-2007
Saedawke
Saedawke

Reputation: 471

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

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

Upvotes: 2

Views: 131

Answers (1)

Sachu
Sachu

Reputation: 7766

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

Upvotes: 4

Related Questions