Professional Web Applications Themes

Oracle TO_CHAR function written in T-SQL - Microsoft SQL / MS SQL Server

I wrote a SQL Server TO_CHAR function which works nearly identically to Oracle's function. It allows a developer to specify a freeform DATE template to convert to VARCHAR. I use it in Data Warehousing to create function based indexes. I had to jump through some programming hoops to keep it deterministic as many SQL Server date function are not....

  1. #1

    Default Oracle TO_CHAR function written in T-SQL

    I wrote a SQL Server TO_CHAR function which works
    nearly identically to Oracle's function. It allows a
    developer to specify a freeform DATE template to convert
    to VARCHAR.
    I use it in Data Warehousing to create function based
    indexes.
    I had to jump through some programming hoops to keep
    it deterministic as many SQL Server date function are not.

    Fred Guest

  2. #2

    Default Oracle TO_CHAR function written in T-SQL

    I wrote this function that is nearly identical to
    Oracle's TO_CHAR function. It's a handy function, which
    allows a developer/report writer to supply a free-form
    template for DATE conversion to VARCHAR.
    I had to jump through some programming hoops to keep
    it deterministic (Many SQL Server DATE functions are not).
    I use it in data warehousing application with a function
    based index (computed column). I included a deterministic
    DAY_OF_WEEK function because DATEPART (dw) is not.
    I thought this maybe useful to others.

    Fred Zimmerman

    CREATE FUNCTION dbo.TO_CHAR
    (Date DATETIME, --Date Value to format
    Template VARCHAR(30)) --Free form template of
    format(see CASE statement for valid fields)
    RETURNS VARCHAR(35)
    WITH SCHEMABINDING
    AS
    BEGIN
    /*
    Copyright Fred Zimmerman 2003 com. Freeware.
    You are free to modify and use but please keep this
    copyright notice.

    This function emulates the Oracle TO_CHAR function on SQL
    Server with limitations
    (No ISO, Julian etc, feel free to add
    functionality).
    The main advantage is it excepts a free form date
    template. A feature lacking in SQL Server.

    NOTE: This is a very low CPU cost function to use.
    All DATENAME and the DATEPART(dw){Day of week} are non-
    deterministic functions in SQL Server
    hence the additional code.
    */

    DECLARE Pointer TINYINT, --Pointer to char
    position in template
    Output VARCHAR(30), --Return value
    Field VARCHAR(30), --Discrete field
    with template
    FieldLen TINYINT --Current Field
    Lenth to extract from template



    SET Pointer = 1
    SET Field = ''
    SET Output = ''
    SET Template = REPLACE(Template,' ','`') --
    Temporally replace spaces with '`'
    WHILE Pointer <= LEN(Template)
    BEGIN
    SET FieldLen = 5 --max. template
    field length
    WHILE FieldLen > 0
    BEGIN
    SET Field = SUBSTRING
    (Template,Pointer,FieldLen)
    IF Field IN
    ('y','yy','yyy','yyyy','q','mm','dd','ww','ddd','h h','ss','
    mi','mon','month','dy','day','A.M.','AM','P.M.','P M','B.C.'
    ,'A.D.','AD','BC')
    BEGIN
    SET Output = CASE Field
    WHEN 'y' THEN
    Output + RIGHT(CONVERT(VARCHAR(4),DATEPART
    (YYYY,Date)),1) --last digit year
    WHEN 'yy' THEN
    Output + RIGHT(CONVERT(VARCHAR(4),DATEPART
    (YYYY,Date)),2) --last 2 digits year
    WHEN 'yyy' THEN
    Output + RIGHT(CONVERT(VARCHAR(4),DATEPART
    (YYYY,Date)),3) --last 3 digits year
    WHEN 'yyyy' THEN
    Output + CONVERT(CHAR(4),DATEPART(YYYY,Date))
    --last 4 digits year
    WHEN 'mm' THEN
    Output + RIGHT('0' + CONVERT(VARCHAR(3),DATEPART
    (mm,Date)),2) --2 digit month
    WHEN 'dd' THEN
    Output + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART
    (dd,Date)),2) --2 digit day of month
    WHEN 'ddd' THEN
    Output + RIGHT('00' + CONVERT(VARCHAR(2),DATEPART
    (dy,Date)),2)--3 digit day of year
    WHEN 'hh' THEN
    Output + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART
    (hh,Date)),2) --hour
    WHEN 'mi' THEN
    Output + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART
    (mi,Date)),2) --minutes
    WHEN 'ss' THEN
    Output + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART
    (ss,Date)),2) --seconds
    WHEN 'ww' THEN
    Output + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART
    (wk,Date)),2) --Week of year
    WHEN 'q' THEN
    Output + CONVERT(CHAR(1),DATEPART(q,Date))
    --quarter
    WHEN 'd' THEN
    Output + CONVERT(CHAR(1),dbo.DayOfWeek(Date))
    --day of week calls UDF
    WHEN 'mon' THEN
    CASE DATEPART(mm,Date) --3 char Month

    WHEN 1 THEN Output + 'Jan'

    WHEN 2 THEN Output + 'Feb'

    WHEN 3 THEN Output + 'Mar'

    WHEN 4 THEN Output + 'Apr'

    WHEN 5 THEN Output + 'May'

    WHEN 6 THEN Output + 'Jun'

    WHEN 7 THEN Output + 'Jul'

    WHEN 8 THEN Output + 'Aug'

    WHEN 9 THEN Output + 'Sep'

    WHEN 10 THEN Output + 'Oct'

    WHEN 11 THEN Output + 'Nov'

    WHEN 12 THEN Output + 'Dec'

    END
    WHEN 'month' THEN
    CASE DATEPART(mm,Date) --Full Month

    WHEN 1 THEN Output + 'January'

    WHEN 2 THEN Output + 'Febuary'

    WHEN 3 THEN Output + 'March'

    WHEN 4 THEN Output + 'April'

    WHEN 5 THEN Output + 'May'

    WHEN 6 THEN Output + 'June'

    WHEN 7 THEN Output + 'July'

    WHEN 8 THEN Output + 'August'

    WHEN 9 THEN Output + 'September'

    WHEN 10 THEN Output + 'October'

    WHEN 11 THEN Output + 'November'

    WHEN 12 THEN Output + 'December'

    END
    WHEN 'day' THEN
    CASE dbo.DayOfWeek(Date)

    WHEN 1 THEN Output + 'Sunday'--Full Day

    WHEN 2 THEN Output + 'Monday'--1st day of
    week also found in DayOfWeek function

    WHEN 3 THEN Output + 'Tuesday'

    WHEN 4 THEN Output + 'Wednesday'

    WHEN 5 THEN Output + 'Thursday'

    WHEN 6 THEN Output + 'Friday'

    WHEN 7 THEN Output + 'Saturday'

    END
    WHEN 'dy' THEN
    CASE dbo.DayOfWeek(Date)

    WHEN 1 THEN Output + 'Sun'--3 Char Day

    WHEN 2 THEN Output + 'Mon'--1st day of
    week also found in DayOfWeek function

    WHEN 3 THEN Output + 'Tue'

    WHEN 4 THEN Output + 'Wed'

    WHEN 5 THEN Output + 'Thu'

    WHEN 6 THEN Output + 'Fri'

    WHEN 7 THEN Output + 'Sat'

    END
    WHEN 'A.M.' THEN
    CASE

    WHEN DATEPART(hh,Date) < 12 THEN Output
    + 'A.M.'

    ELSE Output + 'P.M.'

    END
    WHEN 'P.M.' THEN
    CASE

    WHEN DATEPART(hh,Date) < 12 THEN Output
    + 'A.M.'

    ELSE Output + 'P.M.'

    END
    WHEN 'AM' THEN
    CASE

    WHEN DATEPART(hh,Date) < 12 THEN Output
    + 'AM'

    ELSE Output + 'PM'

    END
    WHEN 'PM' THEN
    CASE

    WHEN DATEPART(hh,Date) < 12 THEN Output
    + 'AM'

    ELSE Output + 'PM'

    END
    WHEN 'A.D.' THEN
    Output + 'A.D.' --SQL Server datetime values are limited
    to 1753 AD
    WHEN 'AD' THEN
    Output + 'AD'
    WHEN 'B.C.' THEN
    Output + 'A.D.'
    WHEN 'BC' THEN
    Output + 'AD'
    ELSE ''
    END

    SET Pointer = Pointer + LEN
    (Field)
    SET FieldLen = 0
    END
    ELSE
    BEGIN
    IF FieldLen = 1 --Irregular
    character, concatenate on output string
    BEGIN
    SET Output = Output +
    Field
    SET Pointer = Pointer +
    LEN(Field)
    END
    SET FieldLen = FieldLen - 1
    END
    END
    END
    SET Output = REPLACE(Output,'`',' ') --Restore spaces
    RETURN Output
    END

    --DROP FUNCTION DayOfWeek
    CREATE FUNCTION dbo.DayOfWeek
    (Date DATETIME)

    RETURNS TINYINT
    WITH SCHEMABINDING
    AS
    /*
    Copyright Fred Zimmerman 2003 com
    This is a deterministic day of week function to replace
    the native SQL Server functions
    which is not deterministic.
    */
    BEGIN

    DECLARE
    DayOfWeek TINYINT,
    Day TINYINT



    --On 1/1/1900, 1-Saturday,2-Sunday, 3-
    Monday (First Day of Week)
    SET Day = 2 + DATEDIFF(day,CONVERT
    (DATETIME,'1/1/1900',101),Date)%7


    SET DayOfWeek = CASE WHEN Day > 7 THEN Day - 7
    ELSE Day
    END

    RETURN DayOfWeek

    END


    Fred Guest

  3. #3

    Default Re: Oracle TO_CHAR function written in T-SQL

    Well, I use Convert function of SQL Server to convert datetime to varchar.


    "Fred Zimmerman" <com> wrote in message
    news:01aa01c35c51$e264f780$gbl... 


    Nikhil Guest

Similar Threads

  1. Oracle Function Call
    By susanvoyce in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: April 22nd, 08:09 PM
  2. to_char bug?
    By Bricklen in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: March 2nd, 02:23 PM
  3. Confused by to_char
    By Ragnar in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: February 8th, 05:47 PM
  4. OS demo written in assembler written in Bash
    By cLIeNUX in forum UNIX Programming
    Replies: 10
    Last Post: January 4th, 10:05 AM
  5. Replies: 2
    Last Post: November 11th, 07:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139