T-SQL String Function: Returns passed date formatted using passed criteria. Format criteria similar to Visual Basic. Saw that I could correct it, so corrected 3/8/04
2007-10-02 (first published: 2002-06-20)
15,443 reads
T-SQL String Function: Returns passed date formatted using passed criteria. Format criteria similar to Visual Basic. Saw that I could correct it, so corrected 3/8/04
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