SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert date to letter based format		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

196 Posts
Posted - 01/14/2013 :  10:25:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 01/14/2013 :  11:18:10  Show Profile  Reply with Quote
You can use convert function for example
SELECT LEFT(CONVERT(VARCHAR(100),GETDATE(),109),11)
Or, more simply
SELECT 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.aspx

Unless 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.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
6997 Posts
Posted - 01/14/2013 :  11:20:42  Show Profile  Reply with Quote
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, 2006


Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519





CODO ERGO SUM
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47189 Posts
Posted - 01/15/2013 :  00:39:51  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page

allan8964
Posting Yak Master

196 Posts
Posted - 01/15/2013 :  09:23:22  Show Profile  Reply with Quote
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!
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 01/15/2013 :  09:40:22  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000