Many people have asked if there is a way to make SQL Server behave the way FORMAT works in VB (and FormatDateTime in VBScript). What they'd like to see is the ability to tell SQL Server to format a date with long date and time, or in MM/DD/YYYY format, instead of having to memorize existing format conversion numbers and/or manipulate the strings themselves. For example, to get today's date in YYYYMMDD format, you currently need to call the following: 
 

SELECT CONVERT(CHAR(8), GETDATE(), 112)

 
What does the 112 mean? Nothing. It's just an arbitrary number representing this specific format (Kalen Delaney's Inside SQL Server 2000 has a detailed explanation of the more commonly-used conversions). 
 
Now, wouldn't it be nice to be able to say this: 
 

SELECT CONVERT(VARCHAR, GETDATE(), 'YYYYMMDD')

 
? 
 
Well, now you can, if you're using SQL Server 2000. I designed this scalar user-defined function for specifically this purpose. 
 

CREATE FUNCTION dbo.FormatDateTime  (      @dt DATETIME,      @format VARCHAR(16)  )  RETURNS VARCHAR(64)  AS  BEGIN      DECLARE @dtVC VARCHAR(64)      SELECT @dtVC = CASE @format        WHEN 'LONGDATE' THEN            DATENAME(dw, @dt)          + ',' + SPACE(1) + DATENAME(m, @dt)          + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))          + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))        WHEN 'LONGDATEANDTIME' THEN            DATENAME(dw, @dt)          + ',' + SPACE(1) + DATENAME(m, @dt)          + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))          + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))          + SPACE(1) + RIGHT(CONVERT(CHAR(20),          @dt - CONVERT(DATETIME, CONVERT(CHAR(8),          @dt, 112)), 22), 11)        WHEN 'SHORTDATE' THEN            LEFT(CONVERT(CHAR(19), @dt, 0), 11)        WHEN 'SHORTDATEANDTIME' THEN            REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0),              'AM', ' AM'), 'PM', ' PM')        WHEN 'UNIXTIMESTAMP' THEN            CAST(DATEDIFF(SECOND, '19700101', @dt)          AS VARCHAR(64))        WHEN 'YYYYMMDD' THEN            CONVERT(CHAR(8), @dt, 112)        WHEN 'YYYY-MM-DD' THEN            CONVERT(CHAR(10), @dt, 23)        WHEN 'YYMMDD' THEN            CONVERT(VARCHAR(8), @dt, 12)        WHEN 'YY-MM-DD' THEN            STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12),          5, 0, '-'), 3, 0, '-')        WHEN 'MMDDYY' THEN            REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0))        WHEN 'MM-DD-YY' THEN            CONVERT(CHAR(8), @dt, 10)        WHEN 'MM/DD/YY' THEN            CONVERT(CHAR(8), @dt, 1)        WHEN 'MM/DD/YYYY' THEN            CONVERT(CHAR(10), @dt, 101)        WHEN 'DDMMYY' THEN            REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0))        WHEN 'DD-MM-YY' THEN            REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-')        WHEN 'DD/MM/YY' THEN            CONVERT(CHAR(8), @dt, 3)        WHEN 'DD/MM/YYYY' THEN            CONVERT(CHAR(10), @dt, 103)        WHEN 'HH:MM:SS 24' THEN            CONVERT(CHAR(8), @dt, 8)        WHEN 'HH:MM 24' THEN            LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)        WHEN 'HH:MM:SS 12' THEN            LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))        WHEN 'HH:MM 12' THEN            LTRIM(SUBSTRING(CONVERT(          VARCHAR(20), @dt, 22), 10, 5)          + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))        ELSE            'Invalid format specified'        END      RETURN @dtVC  END  GO

 
(If you're using SQL Server 7.0, you can't create UDFs; so, I suppose you could put this logic into a stored procedure, and put the result into an output parameter.) 
 
Sample usage: 
 

DECLARE @now DATETIME  SET @now = GETDATE()    PRINT dbo.FormatDateTime(@now, 'LONGDATE')  PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME')  PRINT dbo.FormatDateTime(@now, 'SHORTDATE')  PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME')  PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP')  PRINT dbo.FormatDateTime(@now, 'YYYYMMDD')  PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD')  PRINT dbo.FormatDateTime(@now, 'YYMMDD')  PRINT dbo.FormatDateTime(@now, 'YY-MM-DD')  PRINT dbo.FormatDateTime(@now, 'MMDDYY')  PRINT dbo.FormatDateTime(@now, 'MM-DD-YY')  PRINT dbo.FormatDateTime(@now, 'MM/DD/YY')  PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY')  PRINT dbo.FormatDateTime(@now, 'DDMMYY')  PRINT dbo.FormatDateTime(@now, 'DD-MM-YY')  PRINT dbo.FormatDateTime(@now, 'DD/MM/YY')  PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY')  PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24')  PRINT dbo.FormatDateTime(@now, 'HH:MM 24')  PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12')  PRINT dbo.FormatDateTime(@now, 'HH:MM 12')  PRINT dbo.FormatDateTime(@now, 'goofy')