Return datediff as X Years Y Months internationally

Question

Post reply

Return datediff as X Years Y Months internationally

Andy Reilly

Hall of Fame

Points: 3677
More actions
August 20, 2012 at 3:15 am

#280371

Hi,
What I'm looking to do is have an ininternationalized function that returns datediff between 2 dates formatted as x Years y months - No problem doing that but i need the words "months" and "years" to be international - So if the user is French they get x Ans Y mois. I'm not even sure this is possible without storing these entries either in a table or hard-coded in a function.
Anyone got any ideas?
Cheers
Andy

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

You need a table that localises the words. AFAIK, SQL Server doesn't understand "years" or "ans"

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 2

Cheers Steve,

That's what I thought, just wondered if there was any smart way to do it though 🙁

Andy

Arjen Krap SSC-Addicted Points: 473 More actions · Answer 3

In my opinion an expression like "x years y months" isn't a date but more like the .Net type TimeSpan. So I wouldn't advice you to convert it to dates.

I would rather convert it to months like this: m = x * 12 + y. Of course you will have to parse it, and that where it could get tricky. There are many ways to do this.

If you do want to convert it tot date you can use the DATEFROMPARTS function.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Arjen Krap (8/23/2012)
In my opinion an expression like "x years y months" isn't a date but more like the .Net type TimeSpan. So I wouldn't advice you to convert it to dates.
I would rather convert it to months like this: m = x * 12 + y. Of course you will have to parse it, and that where it could get tricky. There are many ways to do this.
If you do want to convert it tot date you can use the DATEFROMPARTS function.

Can't use DATEFROMPARTS function, this SQL Server 2008 not SQL Server 2012.

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 5

What I need to do is to display the time left in years and months. So knowing tghe number of months left is not a problem what I need to be able to do is to internationalise the string - so if I'm english I get "2 Years 1 Month" If French I get "2 Ans 1 mois" etc

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Something like this?

DECLARE @Years nvarchar(12)

SELECT @Years = CASE @@LANGUAGE

WHEN 'us_english' THEN N'years'

WHEN 'Français' THEN N'ans'

WHEN 'Español' THEN N'años'

-- etc

END

John

Andy Reilly Hall of Fame Points: 3677 More actions · Answer 7

Hi John,

Thanks for that. I realise I can do this I just wondered if there was a way of getting the text out of SQL without hard coding a list. For example you can get the name of the month out of SQL and it returns in the correct language.

Cheers

Andy