As everyone knows, DATE can be formatted in different ways and the usual way will be using CONVERT
Syntax:
SELECT CONVERT(VARCHAR(30), GETDATE(), 106)
Where as 106 can be replaced by 101, 110, etc according to the requirements.
Custom Format:
If we need custom date format(19-March-2009), which is not a pre-defined format in CONVERT, then consider the below:
Syntax:
SELECT DATENAME(DAY,GETDATE()) + '-' + DATENAME(MONTH,GETDATE()) + '-' + DATENAME(YEAR, GETDATE())
I know, this is just a small thing. But still it might help someone who is in search of requirement like this !