One of the reader Nanda of SQLAuthority.com has posted very detailed script of converting any date time in desired format. I suggest every reader of this blog to save this script in your permanent code bookmark and use it when you need it. Let us learn about User Defined Functions.
Refer the function and get familiar yourself with a different format this function support. I have added a few examples of how this function can be used at the end of the article. You can download the whole code in ZIP format as well.
I am not including the entire function over here as it can be very long it is very difficult to format. I strongly encourage that you download the function from the URL which I have listed above. I personally find it very helpful and interesting.
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mm/dd/yy') 'Format mm/dd/yy' GO SELECT [dbo].[ufsFormat] ('8/7/2008', 'hh:mm:ss') 'Format hh:mm:ss' GO SELECT [dbo].[ufsFormat] ('8/7/2008', 'mmm') 'Format mmm' GO SELECT [dbo].[ufsFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM') 'Format Mmm dd yyyy hh:mm:ss:ms AM/PM' GO SELECT [dbo].[ufsFormat] ('8/7/2008', '#') 'Format #' GO
Let me know what you think of this function. I strongly encourage that you try this out. However, in the latest version of SQL Server, there is a better function called FORMAT which can the date and time in a better format.
Let me know what you think of this new blog article SQL SERVER – Learning New Multipurpose FORMAT Function.