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;<span class="kwrd">public</span> <span class="kwrd">override</span> <span class="kwrd">string</span> ToString() { <span class="rem">// Always return ISO Format</span> <span class="kwrd">return</span> dateTimeEx.ToString(<span class="str">"yyyyMMdd HH:mm:ss:fff"</span>); } <span class="kwrd">public</span> <span class="kwrd">bool</span> IsNull { get { <span class="kwrd">return</span> (m_Null); } } <span class="kwrd">public</span> <span class="kwrd">static</span> DateTimeEx Null { get { DateTimeEx h = <span class="kwrd">new</span> DateTimeEx(); h.m_Null = <span class="kwrd">true</span>; <span class="kwrd">return</span> h; } } <span class="kwrd">public</span> <span class="kwrd">static</span> DateTimeEx Parse(SqlString s) { <span class="kwrd">if</span> (s.IsNull) <span class="kwrd">return</span> Null; DateTimeEx u = <span class="kwrd">new</span> DateTimeEx(); <span class="rem">// add more formats if you wish </span> u.dateTimeEx = DateTime.ParseExact(s.ToString(), Common.DateTimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AllowWhiteSpaces); <span class="kwrd">return</span> u; } <span class="rem">/// <summary></span> <span class="rem">/// Converts the datetime to SqlDateTime. if DateTimeEx is less than SqlDateTime it returns 17530101</span> <span class="rem">/// </summary></span> <span class="rem">/// <returns>DateTimeEx converted to SqlDateTime </returns></span> <span class="kwrd">public</span> SqlDateTime ConvertToSqlDateTime() { <span class="kwrd">if</span> (dateTimeEx < <span class="kwrd">new</span> DateTime(1753, 1, 1)) <span class="kwrd">return</span> <span class="kwrd">new</span> SqlDateTime(1753, 1, 1); <span class="kwrd">else</span> <span class="kwrd">return</span> <span class="kwrd">new</span> SqlDateTime(dateTimeEx.Year, dateTimeEx.Month, dateTimeEx.Day, dateTimeEx.Hour, dateTimeEx.Minute, dateTimeEx.Second, dateTimeEx.Millisecond); } <span class="preproc">#region</span> IBinarySerialize Members <span class="kwrd">public</span> <span class="kwrd">void</span> Read(System.IO.BinaryReader r) { dateTimeEx = <span class="kwrd">new</span> DateTime(r.ReadInt64()); } <span class="kwrd">public</span> <span class="kwrd">void</span> Write(System.IO.BinaryWriter w) { w.Write(dateTimeEx.Ticks); } <span class="preproc">#endregion</span>}
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);
<span class="kwrd">return</span> DateTimeEx.Parse(dt.ToString(<span class="str">"yyyyMMdd HH:mm:ss:fff"</span>)); } [SqlFunction] <span class="kwrd">public</span> <span class="kwrd">static</span> 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 = <span class="kwrd">new</span> TimeSpan(dtEnd.Ticks - dtStart.Ticks); <span class="kwrd">if</span> (datePart == <span class="str">"dd"</span> || datePart == <span class="str">"d"</span>) diff = TS.Days; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"yy"</span> || datePart == <span class="str">"yy"</span>) diff = (<span class="kwrd">int</span>)(TS.Days / 365); <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"qq"</span> || datePart == <span class="str">"q"</span>) <span class="rem">// simple quarter difference calcualtion</span> diff = (<span class="kwrd">int</span>)((TS.TotalDays / 365) * 4); <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"mm"</span> || datePart == <span class="str">"m"</span>) <span class="rem">// simple month difference calcualtion</span> diff = (<span class="kwrd">int</span>)((TS.TotalDays / 365) * 12); <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"dy"</span> || datePart == <span class="str">"y"</span>) diff = TS.Days; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"wk"</span> || datePart == <span class="str">"ww"</span>) diff = TS.Days; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"dw"</span> || datePart == <span class="str">"w"</span>) diff = TS.Days / 7; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"hh"</span>) diff = TS.Hours; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"mi"</span> || datePart == <span class="str">"n"</span>) diff = TS.Minutes; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"ss"</span> || datePart == <span class="str">"s"</span>) diff = TS.Seconds; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"ms"</span>) diff = TS.Milliseconds; <span class="kwrd">return</span> diff; } [SqlFunction] <span class="kwrd">public</span> <span class="kwrd">static</span> SqlInt32 DatePartEx(SqlString datePart, DateTimeEx date) { datePart = datePart.Value.ToLower(); DateTime dt = DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AllowWhiteSpaces); <span class="kwrd">int</span> retValue = 0; <span class="kwrd">if</span> (datePart == <span class="str">"dd"</span> || datePart == <span class="str">"d"</span>) retValue = dt.Day; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"yy"</span> || datePart == <span class="str">"yy"</span>) retValue = dt.Year; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"qq"</span> || datePart == <span class="str">"q"</span>) { <span class="kwrd">int</span> quarter = dt.Month / 3; retValue = (quarter == 0 ? 1 : quarter); } <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"mm"</span> || datePart == <span class="str">"m"</span>) retValue = dt.Month; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"dy"</span> || datePart == <span class="str">"y"</span>) retValue = dt.DayOfYear; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"wk"</span> || datePart == <span class="str">"ww"</span>) <span class="rem">// get the week of year with the rule that the week starts on the first day of week</span> <span class="rem">// and the first day of week is monday</span> retValue = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(dt, CalendarWeekRule.FirstDay, DayOfWeek.Monday); <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"dw"</span> || datePart == <span class="str">"w"</span>) retValue = (<span class="kwrd">int</span>)dt.DayOfWeek + 1; <span class="rem">// .net returns 0-6 SQL server returns 1-7</span> <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"hh"</span>) retValue = dt.Hour; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"mi"</span> || datePart == <span class="str">"n"</span>) retValue = dt.Minute; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"ss"</span> || datePart == <span class="str">"s"</span>) retValue = dt.Second; <span class="kwrd">else</span> <span class="kwrd">if</span> (datePart == <span class="str">"ms"</span>) retValue = dt.Millisecond; <span class="kwrd">return</span> retValue; } [SqlFunction] <span class="kwrd">public</span> <span class="kwrd">static</span> SqlInt32 YearEx(DateTimeEx date) { <span class="kwrd">return</span> DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AllowWhiteSpaces).Year; } [SqlFunction] <span class="kwrd">public</span> <span class="kwrd">static</span> SqlInt32 MonthEx(DateTimeEx date) { <span class="kwrd">return</span> DateTime.ParseExact(date.ToString(), Common.DateTimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.AllowWhiteSpaces).Month; } [SqlFunction] <span class="kwrd">public</span> <span class="kwrd">static</span> SqlInt32 DayEx(DateTimeEx date) { <span class="kwrd">return</span> 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 goCREATE 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
|
|
Legacy Comments
|
Marc Brooks
2006-12-17 |
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? |
|
Mladen
2006-12-18 |
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? |
|
kamskyleo
2007-06-11 |
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) . |
|
Mladen
2007-06-11 |
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. |
|
Ole Kristian
2009-03-06 |
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 |
|
Scott Dale Robison
2009-06-05 |
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. |
|
chudson
2009-06-26 |
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? |
|
Mladen
2009-06-26 |
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. |