Convert Date English and French

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Date English and French

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2012-09-30 : 00:14:49

Hi,

I use the following to convert a date.
English:
Select CONVERT(DATE, '1-' + 'jan-2012') --> returns 2012-01-01

French:
Select CONVERT(DATE, '1-' + 'janv.-2012')
returns Conversion failed when converting date and/or time from character string.

Any idea?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-30 : 02:11:31

what is your language ?


select	@@language

for French, try set the language to French before convert.

SET LANGUAGE N'French'

refer to http://msdn.microsoft.com/en-us/library/ms174398.aspx

KH
[spoiler]Time is always against us[/spoiler]

infodemers
Posting Yak Master

183 Posts

Posted - 2012-09-30 : 09:16:50

You are right khtan!

I also found out, I have the remove the dot in the month name to get it work with the language setting!
SET LANGUAGE N'French'
Select CONVERT(DATE, '1-' + 'janv-2012')
SET LANGUAGE N'English'
Select CONVERT(DATE, '1-' + 'jan-2012')

Thanks a lot for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 10:50:09

this is the problem of trying to store dates as text format. Always try to store date values as date itself if possible and pass values in an unambiguos format

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-10-05 : 04:58:44

Always use unambiguous date format http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources