I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

I was playing with some historical data (family tree) and i wanted to store data in sql server. When looking into family trees you reach the minimum datetime value of 1753-01-01 very soon. But .Net can save dates from 1.1.0001 on. So i went looking into using SQL CLR user defined datatype (UDT).

UDT's are interesting because you have to serialize them.

There are 3 ways of doing that:

 - Format.Native
 - Format.UserDefined 
 - Format.Unknown

When Format.Native is used you can only use blittable datatypes. Bol says these are:

bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney, SqlBoolean


So that means no strings, no datatime, no reference datatypes of anykind.

That also means you have to use Format.UserDefined and implement IBinarySerialize interface. That's not realy hard and is evident in code.

I've also added standard date manipulation functions that work the same way as the ones native to Sql Server.

MaxByteSize must be specified so that the server know how much space can be allocated.

 

UTD's differ from standard datatype in that when selected they return their binary representation. You have to use ToString() method to display the proper value.

I've added some standard datetime formats for valid input but you can add more by simply adding them to the Common.DateTimeFormats array.

 

C# code:

DateTimeEx.cs

using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

[Serializable]
[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 200)]
public struct DateTimeEx : INullable, IBinarySerialize 
{
    private DateTime dateTimeEx;    
    private bool m_Null;
    
    public override string ToString()
    {
        // Always return ISO Format
        return dateTimeEx.ToString("yyyyMMdd HH:mm:ss:fff");
    }

    public bool IsNull
    {
        get { return (m_Null); }
    }

    public static DateTimeEx Null
    {
        get
        {
            DateTimeEx h = new DateTimeEx();
            h.m_Null = true;
            return h;
        }
    }

    public static DateTimeEx Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        DateTimeEx u = new DateTimeEx();
        // add more formats if you wish        
        u.dateTimeEx = DateTime.ParseExact(s.ToString(), Common.DateTimeFormats, 
                                                         DateTimeFormatInfo.InvariantInfo, 
                                                         DateTimeStyles.AllowWhiteSpaces);
        return u;
    }
    
    /// <summary>
    /// Converts the datetime to SqlDateTime. if DateTimeEx is less than SqlDateTime it returns 17530101
    /// </summary>
    /// <returns>DateTimeEx converted to SqlDateTime </returns>
    public SqlDateTime ConvertToSqlDateTime()
    {        
        if (dateTimeEx < new DateTime(1753, 1, 1))
            return new SqlDateTime(1753, 1, 1);
        else
            return new SqlDateTime(dateTimeEx.Year, dateTimeEx.Month, dateTimeEx.Day, 
                                   dateTimeEx.Hour, dateTimeEx.Minute, dateTimeEx.Second, 
                                   dateTimeEx.Millisecond);
    }

    #region IBinarySerialize Members
    public void Read(System.IO.BinaryReader r)
    {
        dateTimeEx = new DateTime(r.ReadInt64());
    }

    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(dateTimeEx.Ticks);
    }
    #endregion
}

DateTimeExFunctions.cs

using System;
using System.Globalization;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class DateTimeUDFs
{
    [SqlFunction]
    public static DateTimeEx DateAddEx(SqlString datePart, int number, DateTimeEx date)
    {
        datePart = datePart.Value.ToLower();
        DateTime dt = DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, 
                                                           DateTimeFormatInfo.InvariantInfo, 
                                                           DateTimeStyles.AllowWhiteSpaces);
        if (datePart == "dd" || datePart == "d")
            dt = dt.AddDays(number);
        else if (datePart == "yy" || datePart == "yy")
            dt = dt.AddYears(number);
        else if (datePart == "qq" || datePart == "q")
            dt = dt.AddMonths(number * 3);
        else if (datePart == "mm" || datePart == "m")
            dt = dt.AddMonths(number);
        else if (datePart == "dy" || datePart == "y")
            dt = dt.AddDays(number);
        else if (datePart == "wk" || datePart == "ww")
            dt = dt.AddDays(number * 7);
        else if (datePart == "dw" || datePart == "w")
            dt = dt.AddDays(number);
        else if (datePart == "hh")
            dt = dt.AddHours(number);
        else if (datePart == "mi" || datePart == "n")
            dt = dt.AddMinutes(number);
        else if (datePart == "ss" || datePart == "s")
            dt = dt.AddSeconds(number);
        else if (datePart == "ms")
            dt = dt.AddMilliseconds(number);
        
        return DateTimeEx.Parse(dt.ToString("yyyyMMdd HH:mm:ss:fff"));

    }

    [SqlFunction]
    public static SqlInt32 DateDiffEx(SqlString datePart, DateTimeEx startDate, DateTimeEx endDate)
    {
        datePart = datePart.Value.ToLower();
        DateTime dtStart = DateTime.ParseExact(startDate.ToString(), 
                                               Common.DateTimeFormats, 
                                               DateTimeFormatInfo.InvariantInfo, 
                                               DateTimeStyles.AllowWhiteSpaces);
        DateTime dtEnd = DateTime.ParseExact(endDate.ToString(), 
                                             Common.DateTimeFormats, 
                                             DateTimeFormatInfo.InvariantInfo, 
                                             DateTimeStyles.AllowWhiteSpaces);
        SqlInt32 diff = 0;
        TimeSpan TS = new TimeSpan(dtEnd.Ticks - dtStart.Ticks);
        if (datePart == "dd" || datePart == "d")
            diff = TS.Days;
        else if (datePart == "yy" || datePart == "yy")
            diff = (int)(TS.Days / 365);
        else if (datePart == "qq" || datePart == "q")
            // simple quarter difference calcualtion
            diff = (int)((TS.TotalDays / 365) * 4);
        else if (datePart == "mm" || datePart == "m")
            // simple month difference calcualtion
            diff = (int)((TS.TotalDays / 365) * 12);
        else if (datePart == "dy" || datePart == "y")
            diff = TS.Days;
        else if (datePart == "wk" || datePart == "ww")
            diff = TS.Days;
        else if (datePart == "dw" || datePart == "w")
            diff = TS.Days / 7;
        else if (datePart == "hh")
            diff = TS.Hours;
        else if (datePart == "mi" || datePart == "n")
            diff = TS.Minutes;
        else if (datePart == "ss" || datePart == "s")
            diff = TS.Seconds;
        else if (datePart == "ms")
            diff = TS.Milliseconds;
        return diff;
    }

    [SqlFunction]
    public static SqlInt32 DatePartEx(SqlString datePart, DateTimeEx date)
    {
        datePart = datePart.Value.ToLower();
        DateTime dt = DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, 
                                                           DateTimeFormatInfo.InvariantInfo, 
                                                           DateTimeStyles.AllowWhiteSpaces);
        int retValue = 0;
        if (datePart == "dd" || datePart == "d")
            retValue = dt.Day;
        else if (datePart == "yy" || datePart == "yy")
            retValue = dt.Year;
        else if (datePart == "qq" || datePart == "q")
        {
            int quarter = dt.Month / 3;
            retValue = (quarter == 0 ? 1 : quarter);
        }
        else if (datePart == "mm" || datePart == "m")
            retValue = dt.Month;
        else if (datePart == "dy" || datePart == "y")
            retValue = dt.DayOfYear;
        else if (datePart == "wk" || datePart == "ww")
            // get the week of year with the rule that the week starts on the first day of week
            // and the first day of week is monday
            retValue = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(dt, CalendarWeekRule.FirstDay,
                                                                        DayOfWeek.Monday);
        else if (datePart == "dw" || datePart == "w")
            retValue = (int)dt.DayOfWeek + 1; // .net returns 0-6 SQL server returns 1-7
        else if (datePart == "hh")
            retValue = dt.Hour;
        else if (datePart == "mi" || datePart == "n")
            retValue = dt.Minute;
        else if (datePart == "ss" || datePart == "s")
            retValue = dt.Second;
        else if (datePart == "ms")
            retValue = dt.Millisecond;
        return retValue;
    }

    [SqlFunction]
    public static SqlInt32 YearEx(DateTimeEx date)
    {
        return DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, 
                                                    DateTimeFormatInfo.InvariantInfo, 
                                                    DateTimeStyles.AllowWhiteSpaces).Year;
    }

    [SqlFunction]
    public static SqlInt32 MonthEx(DateTimeEx date)
    {
        return DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, 
                                                    DateTimeFormatInfo.InvariantInfo, 
                                                    DateTimeStyles.AllowWhiteSpaces).Month;
    }

    [SqlFunction]
    public static SqlInt32 DayEx(DateTimeEx date)
    {
        return DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, 
                                                    DateTimeFormatInfo.InvariantInfo, 
                                                    DateTimeStyles.AllowWhiteSpaces).Day;
    }
};

Common.cs

public static class Common
{
    // add more formats if you wish
    public static readonly string[] DateTimeFormats = new string[] { 

"yyyyMMdd HH:mm:ss:fff", "yyyyMMdd",
"yyyy-MM-dd HH:mm:ss:fff", "yyyy-MM-dd",
"yyyy/MM/dd HH:mm:ss:fff", "yyyy/MM/dd",
"yyyyMMdd HH:mm:ss", "yyyy-MM-dd HH:mm:ss",
"yyyy/MM/dd HH:mm:ss"
}; }

 

SQL code:

USE master
go
EXEC sp_configure 'clr enabled', 1
RECONFIGURE
go
CREATE DATABASE testCLR
go
ALTER DATABASE testCLR SET TRUSTWORTHY ON
go
USE testCLR
go

CREATE ASSEMBLY DateTimeEx 
FROM 'D:\Test\DateTimeEx.dll'
WITH PERMISSION_SET = SAFE 
go
CREATE TYPE dbo.DateTimeEx 
EXTERNAL NAME DateTimeEx.[DateTimeEx];
go

-- WITH RETURNS NULL ON NULL INPUT means that if any of the input arguments is null 
-- the function doesn't execute so the result of the call is null
CREATE FUNCTION dbo.DateAddEx(@datePart NVARCHAR(3), @number INT, @date DateTimeEx)
RETURNS DateTimeEx
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DateAddEx
go
CREATE FUNCTION dbo.DateDiffEx(@datePart NVARCHAR(3), @startDate DateTimeEx, @endDate DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT 
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DateDiffEx
go
CREATE FUNCTION dbo.DatePartEx(@datePart NVARCHAR(3), @date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DatePartEx
go
CREATE FUNCTION dbo.YearEx(@date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.YearEx
go
CREATE FUNCTION dbo.MonthEx(@date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.MonthEx
go
CREATE FUNCTION dbo.DayEx(@date DateTimeEx)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME DateTimeEx.DateTimeUDFs.DayEx
GO

-- let's test it if it works
CREATE TABLE TestUDT( date DateTimeEx) 
GO
INSERT INTO TestUDT
SELECT '16500101'
INSERT INTO TestUDT
SELECT '16510101'
INSERT INTO TestUDT
SELECT '11500101'
INSERT INTO TestUDT
SELECT '09500101'
INSERT INTO TestUDT
SELECT NULL
INSERT INTO TestUDT
SELECT '11500326'

SELECT    date, -- this displays types byte array
        date.ToString(), -- so we must use the ToString() to display it properly
        dbo.DateAddEx('d', 5, date).ToString(),
        dbo.DateDiffEx('yy', date, CONVERT(VARCHAR(36), GETDATE(), 120)),
        dbo.DatePartEx('dw', date),
        dbo.YearEx(date),
        dbo.MonthEx(date),
        dbo.DayEx(date)
FROM    TestUDT
go
DROP FUNCTION DateAddEx
DROP FUNCTION DateDiffEx
DROP FUNCTION DatePartEx
DROP FUNCTION YearEx
DROP FUNCTION MonthEx
DROP FUNCTION DayEx
DROP TABLE TestUDT
DROP TYPE dbo.DateTimeEx 
DROP ASSEMBLY DateTimeEx 

go
USE master
DROP DATABASE testCLR
 

kick it on DotNetKicks.com

Print | posted on Saturday, December 16, 2006 10:11 PM | Filed Under [ .Net SQL Server ]

Feedback

Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

I wonder if it wouldn't be possible to convert the DateTimeEx to a DateTime (for the various DateDiffEx, DateAddEx, etc) through the Ticks member instead. That would be tons faster.

Also, some of the Add/Diff/Part code isn't necessarily the same as what SQL Server would return, did you do a side-by-side comparison within the range covered by SQL's DateTime?
12/17/2006 6:36 PM | Marc Brooks
Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

Hmm... interesting idea about ticks... i'll play with that when i get the chance.

i didn't do heavy weight comparing of all possibilties etc... :)

for now i know that these might not get the same results:
DatePartEx - when getting the wk because of the settings of when the week starts etc..
DateDiffEx - month, quarter and some leap year calculations because i used a very
simple algorithm.

I haven't found any others that might behave differently.
any examples that you can prodvide other than those i mentioned above?
12/18/2006 9:21 AM | Mladen
Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

I got this Error on running query, could you explain please what's mean (I'm new to SQLCLR and csharp too)
Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 6522, Level 16, State 2, Line 14
A .NET Framework error occurred during execution of user-defined routine or aggregate "DateAddEx":
System.FormatException: String was not recognized as a valid DateTime.
System.FormatException:
at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)
at DateTimeUDFs.DateAddEx(SqlString datePart, Int32 number, DateTimeEx date)
.
6/11/2007 8:26 AM | kamskyleo
Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

this part of the error message tells you exactly what the error is:
"String was not recognized as a valid DateTime"

so your input string isn't a valid datetime based on the
DateTimeFormats collection which gives you supporeted datetime formats.

just add more formats to DateTimeFormats collection and that'll be it.
6/11/2007 9:37 AM | Mladen
Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

Thanks for great help. I add a little diffrent implementation handling both Max and Min contraints, using the buildt inn min and max objects of SQL dateTime.

/// <summary>
/// Creates a legal SQL DateTime Object. The C# Datetime object covers a larger timespan
/// than the SQL DateTime Object. This method returns SQLDateTime if your datetime value is
/// less than the smalest legal value and SQLDateTime.Max if your datetime is
/// higher than a legal SQLDateTimeValue.
/// </summary>
/// <returns>A datetime within the legal SQLDateTime range</returns>
public static DateTime ConvertToLegalSqlDateTime(DateTime cSharpDateTime)
{
if (cSharpDateTime < ((DateTime)SqlDateTime.MinValue))
{
return (DateTime)SqlDateTime.MinValue;
}
else if (cSharpDateTime > ((DateTime)SqlDateTime.MaxValue))
{
return (DateTime)SqlDateTime.MaxValue;

}
else
{
return cSharpDateTime;
}
}

best regards
Ole Kristian
3/6/2009 10:51 AM | Ole Kristian
Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

Is there a reason why you wouldn't just store the C# DateTime values as a long in the database and use ToBinary & FromBinary methods? I've done that for a particular project and it worked beautifully for my needs.
6/5/2009 11:24 PM | Scott Dale Robison
Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

wouldnt it be easier just to use sql's datetime2 or Datetimeoffset in the table?
6/26/2009 8:03 PM | chudson
Gravatar

# re: Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

no it wouldn't because sql server 2005 doesn't have those datatypes.
6/26/2009 8:04 PM | Mladen
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET