SQL date formatting

Do you need the output to remain datetime data type or is it ok for the value to be a string?

If a string is ok, this will do it

SELECT
CASE DATEPART(WEEKDAY,getdate())
      WHEN 1 THEN 'Sunday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 2 THEN 'Monday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 3 THEN 'Tuesday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 4 THEN 'Wednesday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 5 THEN 'Thursday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 6 THEN 'Friday ' + CONVERT(varchar(10), GETDATE(), 103)
      WHEN 7 THEN 'Saturday ' + CONVERT(varchar(10), GETDATE(), 103)
END AS formatteddate

Muffy thanks thats good but what if I am passing it a date. This only works for todays date right

so what if I am passing it a date via a parameter will it still work?

Just use the date parameter or field

CONVERT(varchar(10), @MyDateParam, 103)

or

CONVERT(varchar(10), MyDateField, 103)

check http://sql.dzone.com/news/custom-date-formatting-sql-ser

Thanks it worked Muffy

Thanks mate