"Nicely" formatted dates in SQL queries

I would expect that many of us wish formatting dates (to remove the "time" portion of the date field) in Transact-SQL or other queries to the NAV database was easier. I found that a commonly-quoted solution of using

convert(varchar, <datefield> ,101)

causes problems in Excel if you are querying the SQL databae directly from Excel - the above function converts the date to a varchar (text) field in Excel, so you can't sort by date if your standard date format is dd/mm/yyyy - all the "January" dates sort together regardless of year.

Well, I received an email from Database Journal today, and they had a link to the following article

http://www.databasejournal.com/features ... r-2008.htm

The contributor published a function that converts dates to date fields with formats similar to the way NAV formats work (not exactly but it reminded me of NAV). For example, to return a date in format mm/dd/yyyy, the function

format_date(<datefield>,'MM/DD/YYYY')

returns the date field as a date field in the format MM/DD/YYYY

Similarly, you can pull any part of the date off, e.g.

format_date(<datefield>,'MM') returns just the month

and

format_date(<datefield>,'MONTH') returns the name of the month.

Lots of formats in this great function!:
YYYY - Year in YYYY Format including century
Yr - Year in YY format
QQ - Display Quarter
MM - Display Month
WW - Diplay Week
DD - Display day
24HH - Display hour in 24 hr format
12HH - Display hour in 12 hr format
MI - Display minutes
SS - Display seconds
MS - Display Milliseconds
MCS - Display MicroSeconds
NS - Display NanoSeconds
DAY - Display day name example: Monday
MONTH- - Display Month name example: August
MON - Display short month name example: Aug
AMPM - Display AM / PM for 12 hr format
TZ - Display time offset
UNIXPOSIX - Display unix posix time. Number of seconds from 1/1/1970 (disabled in this version)
UCASE - Display the result in upper case
LCASE - Display the result in lower case

CAVEAT: The UNIXPOSIX format (number of seconds since 1/1/1970) doesn't work for dates prior to 12/14/1901, so 'empty' dates in NAV that are stored as 1/1/1753 cause the function to fail. I simply removed 2 lines of code to eliminate that format since I had no use for it. There are also 3 other formats that work only in SQL 2008, so if you're running SQL 2005 or earlier you have to remove another 6 lines from the function. Just read the full comment thread below the article and it will document the changes you need to make.