PDA

View Full Version : MS Query Analyzer: how to calculate a time span with two DATETIMES?

Dylan Leblanc
05-15-02, 23:43
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!
alligatorsql.com
05-16-02, 06:13
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
MattR
05-16-02, 09:40
Take a look at DATEDIFF and DATEPART functions in t-sql.
Dylan Leblanc
05-16-02, 15:53
Hi guys, thanks alot! The datediff function works perfectly! I like the ability to specify the time unit, like year, month, day, etc.