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, 2012 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, 2012 at 20:25

2 Answers 2


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.

  • Ahhh, 2012! Oh well, I guess I will write a UDF like you said. Thanks
    – King Chan
    Feb 1, 2012 at 21:14
  • That will be awesome. Feb 10, 2012 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? :-) Jan 25, 2016 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

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.