Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


EDIT:

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

share|improve this question
    
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
up vote 14 down vote accepted

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

Syntax:

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.

share|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? :-) – srutzky Jan 25 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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