As of April 17, 2018, this site is now in read-only mode. To start new Blackbaud Developer’s discussions, you will find Developer Discussions in the Blackbaud CRM Community.

To post or respond to Blackbaud Community discussions, you will have to login using a login. Learn how to login here. If you have questions or need assistance, please email

Converting a standard datetime to a UDT_FUZZYDATE

By: Joseph Styons

Posted on: December 18, 2012

CRM comes with a user-defined type called “UDT_FUZZYDATE”.

If you look at that datatype, you’ll notice that it’s just a CHAR(8) field.

Converting from a standard datetime to a UDT_FUZZYDATE can be done pretty easily using native SQL.

Anybody reading this blog could figure this out on their own, but here it is just to save you the typing.

if exists(select * from information_schema.routines where routine_name = ‘usr_ufn_datetime_to_fuzzydate’) begin
drop function usr_ufn_datetime_to_fuzzydate;

create function usr_ufn_datetime_to_fuzzydate
@dt as datetime
returns udt_fuzzydate
return isnull(convert(char(8),replace(convert(nvarchar(30),@dt,102),’.’,”)),’00000000′);

–tiny test script
declare @now datetime = getdate();
select dbo.usr_ufn_datetime_to_fuzzydate(@now);
select dbo.usr_ufn_datetime_to_fuzzydate(null);

One thought on “Converting a standard datetime to a UDT_FUZZYDATE

Leave a Reply

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean