Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Converting column udt_fuzzydate to datetime in SQL

Posted on 2014-11-28
17
Medium Priority
?
378 Views
Last Modified: 2014-11-30
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
0
Comment
Question by:shmz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
17 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 40470630
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
 
LVL 59

Expert Comment

by:HainKurt
ID: 40470632
post a sample data

selecttop 10 distinct udt_fuzzydate_col from mytable
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 40470634
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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 59

Expert Comment

by:HainKurt
ID: 40470637
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
 
LVL 20

Expert Comment

by:dsacker
ID: 40470638
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
 

Author Comment

by:shmz
ID: 40470649
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
 
LVL 20

Expert Comment

by:dsacker
ID: 40470653
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
 

Author Comment

by:shmz
ID: 40470658
There wasn't any space in my code I tried. Just a typo here.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 40470659
Please provide distinct examples of your values:

SELECT TOP 50 DISTINCT tbl1.col1 FROM YourTable
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40470662
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
 

Author Comment

by:shmz
ID: 40470663
Select to 50 startdate from table1

20130613
20140829
00000000
.....
0
 
LVL 20

Expert Comment

by:dsacker
ID: 40470664
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
 
LVL 59

Expert Comment

by:HainKurt
ID: 40470671
if thats the only exception:

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

Expert Comment

by:Scott Pletcher
ID: 40470672
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
 
LVL 20

Accepted Solution

by:
dsacker earned 2000 total points
ID: 40470676
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
 

Author Comment

by:shmz
ID: 40470699
Thanks all. It is working fine for now.
0
 

Author Closing Comment

by:shmz
ID: 40472877
Thank you
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question