Converting column udt_fuzzydate to datetime in SQL

Udf_fuzzydate would be a local user type created on your SQL Server installation. It is not a standard type.

To help you, please let me know the following:

Thanks.

post a sample data

selecttop 10 distinct udt_fuzzydate_col from mytable

I found this:

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

http://www.bbdevnetwork.com/blog-tags/udt_fuzzydate/

i guess this will work

declare @fuzzy as char(8) = '20141127'
SELECT convert(datetime, @fuzzy, 112)

2014-11-27 00:00:00.000

Select allOpen in new window

Chances are you can convert it to datetime with CONVERT(datetime, YourField).

If that doesn't work the values may not be recognizable by SQL Server, and may simply need some rearranging.

It is data type varchar(8)

I tried this code in the query:
....
, Convert(date time, tbl1.col1,112)
....

Error: conversion of a bar char data type to a date time data type resulted in an out of range value.

Please take the space out between "date time". It's datetime (one word).

If that still causes an error, please provide distinct examples of your values:

SELECT TOP 50 DISTINCT tbl1.col1 FROM YourTable

There wasn't any space in my code I tried. Just a typo here.

Please provide distinct examples of your values:

SELECT TOP 50 DISTINCT tbl1.col1 FROM YourTable

IF the char(8) is in a format that SQL Server can directly convert to a datetime, such as 'YYYYMMDD', then just issue this command:

ALTER TABLE table_name
ALTER COLUMN column_name fuzzydate1 datetime { NULL | NOT NULL } --specify NULL or NOT NULL to match what the column already has

Select to 50 startdate from table1

20130613
20140829
00000000
.....

20130613 and 20140829 would convert easily. It's values like 00000000 that are causing a problem. Do you have any other values outside of 00000000 that do not look like dates?

Please list them if so.

And, what would you want 00000000  to default to as a real date? 1900-01-01? 2000-01-01?

if thats the only exception:

SELECT (case @fuzzy when '00000000' then null else convert(datetime, @fuzzy, 112) end) as fuzzy2date
from mytable

Since that's no known date, most accurate is probably to NULL them out:

UPDATE table_name
SET fuzzy1 = NULL
WHERE fuzzy1 = '00000000'

You have to make sure code using the table will handle NULLs properly.  But you'd have to make sure the code treated' 19000101' of whatever other "non-value" you used too, so might as well use NULL since it's available.

Thanks all. It is working fine for now.

Thank you