Technical Article

VB-like Date Format Function (Corrected Org 3/8/04)

,

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating