get the difference between two dates

[abdelhameed81]

hi all i need to get the difference between 2 dates so i use datediff function in SQL to get it but it doesn't work eveery time it get te same output even if i change the dates i don't know where is the problem select datediff(year,12/2/2000,getdate()) and the output is 106 tank abdelhameed81

[Colin Angus Mackay]

abdelhameed81 wrote:

select datediff(year,12/2/2000,getdate())

Try putting the date inside apostrophes. Also, it is generally advisable to use the ISO format (yyyy-mm-dd) so that internationalisation problems don't arise. e.g. datediff(year, '2000-02-12', getdate())

---

Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[Colin Angus Mackay]

Just to further explain what happend. SQL Server interpreted 12/2/2000 as 12 divided by 2 divided by 2000 which is 0.003. Since the the calendar in SQL Server starts on 1st Janurary 1900 you had actually requested the difference in years between 04:19:20 on 1st January 1900 and midnight on 12th February 2006 (or 2nd December 2006 if you use the American format) which is 106.

---

Upcoming Scottish Developers events: * UK Security Evangelists On Tour (2nd November, Edinburgh) * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog