Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to find number of days between two dates?

Answered how to find number of days between two dates?

  • Saturday, January 26, 2013 5:42 AM
     
     
    Sign In to Vote
    1

    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 ?

    how i can find please Help!

     

All Replies

  • Saturday, January 26, 2013 5:48 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    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
     
      Has Code
    Sign In to Vote
    0
     
    SELECT DATEDIFF(DAY,'01/01/2013','05/01/2013')


    Narsimha

     
  • Saturday, January 26, 2013 6:13 AM
     
      Has Code
    Sign In to Vote
    0
     
    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
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Use DATEDIFF

    SELECT DateDiff(Days,StartDate,EndDate)


    Regards
    satheesh

     
  • Saturday, January 26, 2013 11:29 AM
     
     Proposed Answer
    Sign In to Vote
    1

    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
     
     Proposed Answer Has Code
    Sign In to Vote
    1
     
    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
     
     Proposed Answer Has Code
    Sign In to Vote
    1

    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
     
      Has Code
    Sign In to Vote
    1

    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
     
     
    Sign In to Vote
    0
    Your format is different
     
  • Sunday, January 27, 2013 3:40 AM
     
      Has Code
    Sign In to Vote
    0
     
    SELECT DATEDIFF(DAY,Convert (datetime,'01/01/2013',103),Convert (datetime,'05/01/2013',103)) as Day

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .