Is there a quick way to format a date in SQL to return, lets say the first day of that month?

In ms access i could go Format([date],"mm/01/yyyy") and if the date = 12/15/2005, then it would return 12/01/2005.

I've looked into the Datepart function but can't get it to return the way i'm wanting. Does anyone have a user defined data type or something that may help.

Thanks!!

Here's what I came up with. It works, but if anyone has anything better, please don't hesitate to post.


SELECT convert(varchar,convert(varchar,datepart(mm,Date)) + '/01/'+ convert(varchar,datepart(yyyy,Date)),101) AS Month

http://www.mssqlcity.com/FAQ/Devel/first_month_day.htm
http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk