Converting column udt_fuzzydate to datetime in SQL

Hi,
I have a column in database that is of udt_fuzzydate type.

How can it be modified to date time in SQL query?

Thanks
shmzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
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:

What is the true data type of that user type udf_fuzzydate (hover over it, and you should see something like varchar(size), varchar, nvarchar(size), nvarchar, text, etc)?
Please provide some distinct examples of what the values look like in that column.
Thanks.
0
HainKurtSr. System AnalystCommented:
post a sample data

selecttop 10 distinct udt_fuzzydate_col from mytable
0
HainKurtSr. System AnalystCommented:
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/
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

HainKurtSr. System AnalystCommented:
i guess this will work

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

2014-11-27 00:00:00.000

Open in new window

0
dsackerContract ERP Admin/ConsultantCommented:
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.
0
shmzAuthor Commented:
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.
0
dsackerContract ERP Admin/ConsultantCommented:
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
0
shmzAuthor Commented:
There wasn't any space in my code I tried. Just a typo here.
0
dsackerContract ERP Admin/ConsultantCommented:
Please provide distinct examples of your values:

SELECT TOP 50 DISTINCT tbl1.col1 FROM YourTable
0
Scott PletcherSenior DBACommented:
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
0
shmzAuthor Commented:
Select to 50 startdate from table1

20130613
20140829
00000000
.....
0
dsackerContract ERP Admin/ConsultantCommented:
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?
0
HainKurtSr. System AnalystCommented:
if thats the only exception:

SELECT (case @fuzzy when '00000000' then null else convert(datetime, @fuzzy, 112) end) as fuzzy2date
from mytable
0
Scott PletcherSenior DBACommented:
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.
0
dsackerContract ERP Admin/ConsultantCommented:
If you want a value that SQL doesn't recognize to have some default value, you could work with something along these lines:
SELECT  CASE
            WHEN ISDATE(tbl1.col1) = 1 THEN CONVERT(datetime, tbl1.col1)
            ELSE '1900-01-01'
        END     AS ConvertedDate
FROM    YourTable

Open in new window

You can even get fancier than that, but this gives you an idea of how you can deal with those values that are not immediately recognizable to SQL Server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shmzAuthor Commented:
Thanks all. It is working fine for now.
0
shmzAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.