CustomDateFormat.cs #blog

CustomDateFormat.cs

// http://spaghettidba.com/2012/03/23/sql-server-and-custom-date-formats/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString dateFormat(DateTime value, String formatString)
    {
        return new SqlString(value.ToString(formatString));
    }


    private struct ReturnValues
    {
        public SqlString Value;
    }

    private static void FillValues(object obj, out SqlString TheValue)
    {
        ReturnValues ReturnVals = (ReturnValues)obj;
        TheValue = ReturnVals.Value;
    }


    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, 
        IsDeterministic = true, 
        IsPrecise = true, 
        SystemDataAccess = SystemDataAccessKind.None, 
        FillRowMethodName = "FillValues", 
        TableDefinition = "formattedDate nvarchar(50)")]
    public static System.Collections.IEnumerable TVF_Streaming(DateTime value, String formatString)
    {
        ReturnValues Vals = new ReturnValues(); 
        Vals.Value = new SqlString(value.ToString(formatString)); 
        yield return Vals;
    }

};