Funcs for Date Formatting (Like Oracle TO_CHAR)

CREATE FUNCTION dbo.RJZeroN
/*
--
-- Author:  Jeff Burton
-- Date:    03/24/2004
-- Name:    Function dbo.RJZeroN
-- Purpose: Right-justify and zero fill the passed integer value @InValue
--          into a character string (up to 20 characters to hold the
--          largest SQL Server integer value) of passed size @Length.  If
--          value cannot be formatted NULL is returned.
--
-- Return Value:  VARCHAR(20)
--
*/(
@InValue BIGINT
,@Length TINYINT
)
RETURNS VARCHAR(20)
AS

BEGIN

   DECLARE @ReturnValue VARCHAR(20),
           @InValueLen INT,
           @InValueSignChar VARCHAR(1),
           @InValueSignCharLen INT

  SET @InValueLen = LEN(@InValue)

  IF @InValue < 0 
     SET @InValueSignChar = '-'
  ELSE
     SET @InValueSignChar = ''

  SET @InValueSignCharLen = LEN(@InValueSignChar)

   SET @ReturnValue = 
      CASE
         WHEN (ISNULL(@Length,0) NOT BETWEEN 1 AND 20)
         OR (@InValueLen > @Length) OR (@InValue IS NULL)
            /* invalid length or input value argument */            THEN NULL
         ELSE
            @InValueSignChar
            +REPLICATE('0',@Length - @InValueLen)
            +CAST(ABS(@InValue) AS VARCHAR(20))  
      END

   RETURN @ReturnValue

END

GO

CREATE  FUNCTION dbo.FormatDate
/*
--
-- Author:        Jeff Burton
-- Date:          02/17/2004
-- Name:          Function dbo.FormatDate
-- Purpose:       Format passed date according to passed date formatting
--                string.  This function mimics the functionality of
--                Oracle PL/SQL function "to_char" for date formatting.
-- -------------
-- Restrictions:
-- -------------
--
-- (1) Date parts are right justified and zero-filled.  (The Oracle PL/SQL
-- "to_char" function does not do this but this format should be more 
-- consistent/useful.)
--
-- (2) The following date format codes are not handled (but this s.p.
-- can be modified for them if needed): CC, SCC, E, EE, IYY, IY, I, J, RM, RR, RRRR, 
-- SSSSS, TZD, TZH, TZM, TZR, "Y,YYY", YEAR, SYEAR, SYYYY, W, YYY.
--
-- (3) Where there is a common date format code common to Oracle PL/SQL and 
-- SQL2K T-SQL, date formatting for T-SQL is used: 
--
-- DAY (T-SQL format "WEEKDAY" or "DW" should be used instead.) 
-- DY (T-SQL "contrived" format "DAYAB" should be used instead.)
--
-- HH (If AM/A.M./PM/P.M. is in the @dateformatstring this is same as   
--     T-SQL format "hh".  T-SQL "contrived" format "HH12" should be used
--     instead.)
--
-- MONTH (T-SQL "contrived" format "monthfull" should be used instead.)
--
-- (4) T-SQL date formats m/n/d/s/y/day are not supported for use by this function 
-- because these letter appear in month or day names.  Workaround: Use 
-- mm/mi/dd/ss/dy/dd codes respectively instead.
--
-- ----------------------------------
-- Valid Date Formatting Codes 
-- (return same values as T-SQL 
-- func. DATEPART except where noted)
-- ----------------------------------
-- 
-- Code
-- -------------
-- year, yy, yyyy 
-- quarter, qq, q 
-- month, mm 
-- mon:              Contrived. Returns the month name abbrev (e.g. Jan).
-- monthfull:        Contrived. Returns the month name (e.g. January).
-- dayofyear dy 
-- dd 
-- dayab             Contrived. Returns the day name abbrev (e.g. Sun).
-- week, wk, ww 
-- weekday, dw        
-- hour, hh 
-- hh24              Contrived for Oracle date format "hh24".
-- hh12              Contrived for Oracle date format "hh12".
-- minute, mi 
-- second, ss
-- hs                Contrived for hundredths of seconds 
-- millisecond, ms 
-- 
--
*/(
@InputDate DATETIME
,@DateFormatString NVARCHAR(80)
)
RETURNS NVARCHAR(80)
AS

BEGIN

   DECLARE @FormattedDate NVARCHAR(80),
           @HH12Hours VARCHAR(2),
           @MeridianSpecified BIT,
           @Meridian VARCHAR(2)

   SET @FormattedDate = CAST(@DateFormatString AS NVARCHAR(80))

   IF 
   CHARINDEX('AM',@DateFormatString) > 0 
   OR CHARINDEX('A.M.',@DateFormatString) > 0 
   OR CHARINDEX('PM',@DateFormatString) > 0
   OR CHARINDEX('P.M.',@DateFormatString) > 0
      BEGIN
         SET @MeridianSpecified=1
      END
   ELSE
      BEGIN
         SET @MeridianSpecified=0
      END

   IF DATEPART(hh,@InputDate) <= 12
      BEGIN
         SET @HH12Hours = dbo.RJZeroN(DATEPART(hh,@InputDate),2)
         SET @Meridian='AM'
      END
   ELSE
      BEGIN
         SET @HH12Hours = dbo.RJZeroN
                       (DATEPART(hh,@InputDate) - 12 ,2 )
         SET @Meridian='PM'
      END

   SET @FormattedDate =  REPLACE(@FormattedDate,'dayofyear',
                         dbo.RJZeroN(DATENAME(dayofyear,@InputDate),3))

   /* 'ddd' is an Oracle PL/SQL format code (same as T-SQL 'dayofyear' code). */   SET @FormattedDate = REPLACE(@FormattedDate,'ddd',
                         dbo.RJZeroN(DATENAME(dayofyear,@InputDate),3))

   /* 'dayab' is contrived replacement for Oracle PL/SQL format 'dy'. */   SET @FormattedDate = REPLACE(@FormattedDate,'dayab',
                        SUBSTRING(DATENAME(weekday,@InputDate),1,3))

   SET @FormattedDate = REPLACE(@FormattedDate,'monthfull',
                        DATENAME(month,@InputDate))

   SET @FormattedDate = REPLACE(@FormattedDate,'month',
                        dbo.RJZeroN(DATEPART(month,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'mm',
                        dbo.RJZeroN(DATEPART(month,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'mon',
                        SUBSTRING(DATENAME(month,@InputDate),1,3))

   SET @FormattedDate = REPLACE(@FormattedDate,'year',
                        dbo.RJZeroN(DATENAME(year,@InputDate),4))
      
   SET @FormattedDate = REPLACE(@FormattedDate,'yyyy',
                        dbo.RJZeroN(DATENAME(year,@InputDate),4))

   /* 'Iyyy' is an Oracle PL/SQL format code (same as T-SQL code 'yyyy'). */   SET @FormattedDate = REPLACE(@FormattedDate,'Iyyy',
                        dbo.RJZeroN(DATENAME(year,@InputDate),4))

   SET @FormattedDate = REPLACE(@FormattedDate,'yy',
                        dbo.RJZeroN(DATENAME(year,@InputDate),4))
      
   SET @FormattedDate = REPLACE(@FormattedDate,'quarter',
                        dbo.RJZeroN(DATENAME(quarter,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'qq',
                        dbo.RJZeroN(DATENAME(quarter,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'q',
                        dbo.RJZeroN(DATENAME(quarter,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'dd',
                        dbo.RJZeroN(DATENAME(day,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'dy',
                        dbo.RJZeroN(DATENAME(dayofyear,@InputDate),3))

   SET @FormattedDate = REPLACE(@FormattedDate,'weekday',
                        DATENAME(weekday,@InputDate))

   SET @FormattedDate = REPLACE(@FormattedDate,'dw',
                        DATENAME(weekday,@InputDate))

   SET @FormattedDate = REPLACE(@FormattedDate,'week',
                        dbo.RJZeroN(DATENAME(week,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'wk',
                        dbo.RJZeroN(DATENAME(week,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'ww',
                        dbo.RJZeroN(DATENAME(week,@InputDate),2))


   /* 'Iw' is an Oracle PL/SQL format code (same as T-SQL format code 'dw'). */   SET @FormattedDate = REPLACE(@FormattedDate,'Iw',
                        DATENAME(weekday,@InputDate))

   /* 'hh24' is an Oracle PL/SQL format code (same as T-SQL 'hh' code). */   SET @FormattedDate = REPLACE(@FormattedDate,'hh24',
                        dbo.RJZeroN(DATENAME(hour,@InputDate),2))

   /* 'hh12' is an Oracle PL/SQL format code. */   SET @FormattedDate = REPLACE(@FormattedDate,'hh12',
                        @HH12Hours)

   IF @MeridianSpecified=0
      BEGIN
         SET @FormattedDate = REPLACE(@FormattedDate,'hour',
                              dbo.RJZeroN(DATENAME(hour,@InputDate),2))

         SET @FormattedDate = REPLACE(@FormattedDate,'hh',
                              dbo.RJZeroN(DATENAME(hour,@InputDate),2))
      END

   ELSE
      BEGIN
         SET @FormattedDate = REPLACE(@FormattedDate,'hour',
                              @HH12Hours)

         SET @FormattedDate = REPLACE(@FormattedDate,'hh',
                              @HH12Hours)
      END

   SET @FormattedDate = REPLACE(@FormattedDate,'minute',
                        dbo.RJZeroN(DATENAME(minute,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'millisecond',
                        dbo.RJZeroN(DATENAME(millisecond,@InputDate),3))

   SET @FormattedDate = REPLACE(@FormattedDate,'mi',
                        dbo.RJZeroN(DATENAME(minute,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'second',
                        dbo.RJZeroN(DATENAME(second,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'ss',
                        dbo.RJZeroN(DATENAME(second,@InputDate),2))

   SET @FormattedDate = REPLACE(@FormattedDate,'hs',
                                dbo.RJZeroN
                                (ROUND(DATEPART(millisecond,@InputDate),-1)/10 ,2)
                                )

   SET @FormattedDate = REPLACE(@FormattedDate,'ms',
                        dbo.RJZeroN(DATENAME(millisecond,@InputDate),3))

   /* 'ff' is an Oracle PL/SQL format code (same as T-SQL 'ms' code). */   SET @FormattedDate = REPLACE(@FormattedDate,'ff',
                        dbo.RJZERON(DATENAME(millisecond,@InputDate),3))

   RETURN @FormattedDate

END

GO