Results 1 to 4 of 4
  1. #1
    Join Date
    May 2002
    Location
    Vancouver Canada
    Posts
    26

    Unanswered: MS Query Analyzer: how to calculate a time span with two DATETIMES?

    Hi there, we have an assignment in school and one of the questions is to show the years of work experience that some nurses have.

    I have figured out that if you have two columns that are the DATETIME datatype (which we have in the assignment), you can subtract one from another to get a date. I did a little test and it comes back as Jan xx 1900, where xx is the number of days.


    I did this:

    CREATE TABLE DateTest (

    dateFrom DATETIME,
    dateTo DATETIME

    )

    INSERT INTO DateTest VALUES ('april 17 2002', 'april 20 2002')




    Which seems fine.

    Then I went:

    SELECT * FROM DATETEST


    and got:

    dateFrom dateTo
    ----------------------- -------------------------
    2002-04-17 00:00:00.000 2002-04-20 00:00:00.000



    Seems fair enough.

    Then:

    SELECT (dateTo - datefFom) AS timeSpan FROM DateTest

    and got:

    timeSpan
    ---------------------------
    1900-01-04 00:00:00.000


    So you see how it comes back as Jan 4th, to indicate the 4 days.

    How can I get it to come back as simply 4 days? Is this supported???

    Thanks for your help!

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    one way is to cast the result into int. Use something like this:

    SELECT CAST(datefrom-dateto AS INT) FROM ....

    Hope that helps ?

    Regards
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Take a look at DATEDIFF and DATEPART functions in t-sql.
    Thanks,

    Matt

  4. #4
    Join Date
    May 2002
    Location
    Vancouver Canada
    Posts
    26
    Hi guys, thanks alot! The datediff function works perfectly! I like the ability to specify the time unit, like year, month, day, etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •