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 only takes a minute to sign up.
Sign up to join this community
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())
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.
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
