Selecting the date part and formatting it are different tasks. I keep typed dates for calculations and text for display.
SQL SERVER – UDF – Function to Display Current Week Date and Day – Weekly Calendar
A script and UDF that show each current week date with its day, from Sunday to Saturday, using DATEPART and DATEADD. Handy for a weekly calendar report.
SQL SERVER – Script/Function to Find Last Day of Month
A one line DATEADD query to find the last day of month for any date. It handles leap years: February 2008 gives the 29th and 2007 the 28th.
SQL SERVER – Query to Find First and Last Day of Current Month – Date Function
One query to find the First and Last Day of Current Month in SQL Server, plus the last day of the previous month and the first day of the next.
SQL SERVER – UDF – User Defined Function to Find Weekdays Between Two Dates
A user defined function that returns the number of weekdays between two dates, skipping Saturday and Sunday. It was written by Alexander Chigrik.
SQL SERVER – @@DATEFIRST and SET DATEFIRST Relations and Usage
How SET DATEFIRST changes the first day of the week, and how @@DATEFIRST returns the current value, which also changes with SET LANGUAGE. Simple examples.
SQL SERVER – SQL Commandments – Suggestions, Tips, Tricks
My 20 SQL Commandments for SQL Server, rewritten from an Oracle article: know your data, test with realistic data, write identical statements, and more tips.