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.
| Author |
Topic |
|
allan8964
Posting Yak Master
249 Posts |
 Posted - 2013-01-14 : 10:25:21
|
| Hi there,I need convert a date value to different language based formats, like make 12/23/2012 to Dec. 23, 2012 in English and French. I know I can create a table to hold all these abbreviations and join the table get the abbrev. by using month() function thru numbers. But is there any more direct way to achieve that?Thanks in advance. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-14 : 11:18:10
|
You can use convert function for exampleSELECT LEFT(CONVERT(VARCHAR(100),GETDATE(),109),11) Or, more simplySELECT CONVERT(VARCHAR(11),GETDATE(),109) . The result depends on the language setting on the computer. If you are on SQL 2012, you can use the FORMAT function, which is more flexible - http://msdn.microsoft.com/en-us/library/hh213505.aspxUnless there is a compelling reason to do the formatting in SQL, usually it is better to do the formatting and conversions at the client side or other end-point where the data is consumed. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2013-01-14 : 11:20:42
|
| Look at the code for column DATE_FORMAT_MMM_D_YYYY in the function on this link. It produces a text date in format MMM D, YYYY.Example = Jan 4, 2006Date Table Function F_TABLE_DATEhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519CODO ERGO SUM |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-15 : 00:39:51
|
quote:
Originally posted by allan8964
Hi there,I need convert a date value to different language based formats, like make 12/23/2012 to Dec. 23, 2012 in English and French. I know I can create a table to hold all these abbreviations and join the table get the abbrev. by using month() function thru numbers. But is there any more direct way to achieve that?Thanks in advance.
why should you be doing this at database end? This looks like formatting requirement which is best dealt at front end application where you've lots of formatting functions to do the same.Doing this in sql is kind of overkill and can cause issues in case you use converted values for further date manipulation------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
allan8964
Posting Yak Master
249 Posts |
Posted - 2013-01-15 : 09:23:22
|
| Hi James K, thanks for the code. It works but I need a dot after abbreviation and a comma after the number. I use this in sql report. Actually I use the report VB script to achieve that. Thanks.MVJ, I like your code as well. It looks like a big library which may help me somewhere else. Thanks.Visakh16, i think your opinion is the same James' which I took, by using something at the end of the report, good advice.Thank you guys again! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-15 : 09:40:22
|
If you are on sql server 2012 then have a look at the new FORMAT() function.
Too old to Rock'n'Roll too young to die. |
|
|
|
|
|
|
|
|
Convert date to letter based format