All I found is a list of predefined DateFormat that I can choose from, like this

As I remember in MySQL (and PostgeSQL too?) you can define your Date Format:

DATE_FORMAT(now(),'&m_%Y') --for 02_2012 etc.

Does SQL Server have the same thing? I see people have to write a function to do such thing, does it have a built-in one?


I just found the DatePart function. It can take Month as number, but always returns 1 digit, even I use datePart(MM, getdate())

  • Possible duplicate of stackoverflow.com/questions/8202257/… – GSerg Feb 2 '12 at 0:20
  • 1
    Typically, formatting should be handled by the client, not the database. That being said, if you must format the database output, Martin Smith has already provided options. – JSR Feb 2 '12 at 20:25

Not yet.

You need to use CONVERT with a style parameter or hack something together with DATEPART or DATENAME.

SQL Server 2012 will have the FORMAT function though that accepts a .NET Framework format string


FORMAT ( value, format [, culture ] )

Example Usage

SELECT FORMAT(getdate(), 'dd/MM/yyyy', 'en-US' ) 

You could always use CLR integration and create your own UDF that does the same thing for 2005 or 2008.

| improve this answer | |
  • Ahhh, 2012! Oh well, I guess I will write a UDF like you said. Thanks – King Chan Feb 1 '12 at 21:14
  • That will be awesome. – Gordon Bell Feb 10 '12 at 16:02
  • 1
    @KingChan and Martin: Regarding the use of SQLCLR to accomplish this in SQL Server 2005 / 2008 / 2008 R2, there is always the option of downloading the Free version of SQL# (which I am the author of, but Date_Format is free) and then there is no need to do any coding. I have a few examples of both SQL#.Date_Format and the built-in FORMAT on this S.O. answer: Is there a way to get dates with custom formats in SQL Server? :-) – Solomon Rutzky Jan 25 '16 at 15:43

If your goal is simply formatted output and it doesn't need to remain in the datetime datatype, using CONVERT with a format code is an excellent tool. A list of formats and their respective codes can be found here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.