T-SQL String Function: Returns passed date formatted using passed criteria. Format criteria similar to Visual Basic.
Sorry, prior script had a reference to one of my internal UDFs :(.
2007-10-02 (first published: 2002-06-20)
15,459 reads
T-SQL String Function: Returns passed date formatted using passed criteria. Format criteria similar to Visual Basic.
Sorry, prior script had a reference to one of my internal UDFs :(.
Create Function fn_FormatDate (@ADate DateTime, @Format VarChar(200))
RETURNS VarChar(200)
AS
BEGIN
/*
Returns passed date formated with passed format string.
Adhears to standard VB format() conventions except for AM/PM
Format keys:
y= day of year
yy= 2 digit year
yyyy= 4 digit year
q= quarter 1|2|3|4
w= Week of year 1-51
m= month 1-12
mm= month 01-12
mmm= Three charater month Jan-Dec
mmmm= month January-December
d= day of month 1-31
dd= day of month 01-31
ddd = weekday Sun-Sat
dddd= weekday Sunday-Saturday
HH= hours 00-23
NN= minutes 00-59
SS= seconds 00-59
MS= milliseconds 0-999
Example usage:
print dbo.fn_formatDate(getdate(), 'ddd mmmm d, yyyy HH:NN:SS.MS')
*/declare @rv VarChar(200)
select @rv = Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(@Format, 'HH', Right('00' + Convert(VarChar(2),DatePart( HH, @ADate)), 2))
, 'NN', Right('00' + Convert(VarChar(2),DatePart( MI, @ADate)), 2))
, 'SS', Right('00' + Convert(VarChar(2),DatePart( SS, @ADate)), 2))
, 'MS', right('000' + convert(VarChar(3), DatePart( MS, @ADate)), 3))
, 'YYYY', DateName( YYYY, @ADate))
, 'YY', Right(Convert(VarChar(4), DatePart( YY, @ADate)), 2))
, 'MMMM', '~')
, 'MMM', '!')
, 'MM', Right('00' + Convert(VarChar(2),DatePart( MM, @ADate)), 2))
, 'M', DatePart( M, @ADate))
, 'Y', DatePart( DY, @ADate))
, 'DDDD', '@')
, 'DDD', '`')
, 'DD', Right('00' + Convert(VarChar(2),DatePart( DD, @ADate)), 2))
, 'Q', DatePart( q, @ADate))
, 'W', DatePart( wk, @ADate))
, 'D', DatePart( DD, @ADate))
, '~', DateName( MM, @ADate))
, '!', Left(DateName( MM, @ADate),3) )
, '@', DateName(DW, @ADate))
, '`', Left(DateName( DW, @ADate),3))
Return (rtrim(@rv))
END