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!

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

Take a look at DATEDIFF and DATEPART functions in t-sql.

Hi guys, thanks alot! The datediff function works perfectly! I like the ability to specify the time unit, like year, month, day, etc.