Convert/Format a column in a select statement sql server 2005..

Question

I have a sql query which is select DateOfBirth from people and it shows up in the result pane as

DateOfBirth
07/07/2010 5:08:02
07/09/2010 5:08:02
07/13/2010 5:08:02

I want to format,

07/Jul/2010 
09/Jul/2010
13/Jul/2010

NOTE: DateOfBirth column has datatype nvarchar(50) not datetime...

Answer 1

This is a little tricky as the best way to do this is to take the varchar convert it into a datetime and then format it. Annother complication is that the format you want is not a format that SQLServer will output.

So.

SELECT CONVERT(DateTime, DateOfBirth) from people

will get you the date time and we can then convert it to a string format as follows

SELECT CONVERT(DateTime, DateOfBirth), 106) from people

this will produce the string output 'dd Mon YYYY'

then its just a matter of replacing the spaces with '/'

SELECT REPLACE(CONVERT(varchar, CONVERT(DateTime, DateOfBirth), 106), ' ','/')  FROM people

will get you the format you want.