how to find number of days between two dates?

# how to find number of days between two dates?

• Saturday, January 26, 2013 5:42 AM

i have those  fields in my table CBS

Leave Date       ReturnDate             Number Of Days

01/01/2013       05/01/2013                    ?

i Want to calculate Number of Days Between those dates ?

### All Replies

• Saturday, January 26, 2013 5:48 AM
Moderator

Use DATEDIFF( DD, LeaveDate, ReturnDate) .

More on datetime functions:

http://www.sqlusa.com/bestpractices/datetimeconversion/

Always use YYYY-MM-DD string date format.

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

• Saturday, January 26, 2013 5:50 AM

`SELECT DATEDIFF(DAY,'01/01/2013','05/01/2013')`

Narsimha

• Saturday, January 26, 2013 6:13 AM

`SELECT DATEDIFF(DAY,'01/01/2013','05/01/2013') as Day`

It Displas Wrog result of  120 day, And the Deferance Is only 4 day

• Saturday, January 26, 2013 6:31 AM

Hi,

Use DATEDIFF

`SELECT DateDiff(Days,StartDate,EndDate)`

Regards
satheesh

• Saturday, January 26, 2013 11:29 AM

This is because 05/01/2013 is not unambiguous. Crazy as you may think there are people who think that 05/01/2013 is May 1st 2013. I b these funny people are called Americans, so when go and create a database engine, they make that they default interpretation.

You can change the date interpretation with the command "SET DATEFORMAT dmy", but better is to use the date format YYYYMMDD, which cannot be misinterpreted. That is:

SELECT dateddiff(DAY, '20130101', '20130105')

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Saturday, January 26, 2013 1:46 PM

`SELECT DATEDIFF(DAY,'01/01/2013','05/01/2013') as Day`

It Displas Wrog result of  120 day, And the Deferance Is only 4 day

Are the date columns in your table a date or datetime data type?  In that case, I would expect the DATEDIFF to work as expected.  But if you pass data literals to the function, you need to either use an ISO 8601 date format (a best practice) or specify literals that match the session DATEFORMAT setting (which is apparently MDY):

```--match DATEFORMAT
SELECT DATEDIFF(DAY,'01/01/2013','01/05/2013') as Day
--ISO 8601
SELECT DATEDIFF(DAY,'20130101','20130105') as Day```

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

• Saturday, January 26, 2013 2:05 PM

The problem is database cannot interpret 05/01/2013 as 5th Jan or 1st May unless you say which format you follow ddmmyyyy or mmddyyyy

So the safer option is to send the dates in ISO format

`SELECT DATEDIFF(DAY,'20130101','20130105') as Day`

Regards
satheesh

• Saturday, January 26, 2013 2:13 PM
Moderator

Crazy as you may think there are people who think that 05/01/2013 is May 1st 2013. I b these funny people are called Americans, so when go and create a database engine, they make that they default interpretation.

That is hilarious!

Look at another funny thing Americans do with currency numbers.

```-- American currency formatting
SELECT FORMAT(987654321.77,'c2','en-US');  -- \$987,654,321.77

-- German currency formatting
SELECT FORMAT(987654321.77,'c2','de-DE');  -- 987.654.321,77 €

-- Swedish currency formatting
SELECT FORMAT(987654321.77,'c2','se-SE');  -- 987.654.321,77 kr```

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

• Sunday, January 27, 2013 3:30 AM

`SELECT DATEDIFF(DAY,Convert (datetime,'01/01/2013',103),Convert (datetime,'05/01/2013',103)) as Day`