Solved

Convert char(24) to datetime

Posted on 2014-04-10
13
534 Views
Last Modified: 2014-04-15
Hello experts,

I am trying to convert a char(24) field to datetime but it does not work, please help?

A sample data would be:
   SELECT top 1 convert(datetime, '2014-03-05 17:03:18.518') from CounterData

and obviously it works. However, when I replace the date and time data with the field name which has the data type of char(24), it fails:
   select  top 1  convert(datetime, CounterDateTime) from CounterData

(The above query also should return '2014-03-05 17:03:18.518')

The error message is:
   Conversion failed when converting date and/or time from character string.

How can I make this work easily without managing the data char by char? By the way, I am using MS SQL 2008.

Thank you in advance
0
Comment
Question by:bozer
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
You will need to provide us with same sample data, we have no idea what is in that field, and it's likely to be bad data that causes issues.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
perhaps try this to investigate the data quality
SELECT
     CounterDateTime
   , isdt
   , case when isdt = 1 then convert(datetime,CounterDateTime) else null end
FROM CounterData
CROSS APPLY (select isdate(CounterDateTime) as isdt ) ca1
;

Open in new window

0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
SELECT top 1 convert(datetime, '2014-03-05 17:03:18.518') from CounterData

Is working fine as you pointed out.

conv no alisconv with alias
Give example of your table to check the other part using table column.
0
 

Author Comment

by:bozer
Comment Utility
Hello experts,

PortletPaul, I want to use the CounterDateTime in a Select query in 'Where' clause, I will be comparing it with 'current time - 5 min' So I will have to get back to you if your solution works.

hnasr,
Yes that one works, what does not work is this:
   select  top 1  convert(datetime, CounterDateTime) from CounterData
which exactly returns the same data: '2014-03-05 17:03:18.518'
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
Comment Utility
The reason you are getting an error is that somewhere in the field CounterDateTime there is a string that cannot be converted to datetime

e.g.
  30th Feb, even if in the right format
, a time of 25:30
, "Deep Purple Rocks"

these just will not convert into datetime values

You need to locate such problems so that a solution can be found.
0
 

Accepted Solution

by:
bozer earned 0 total points
Comment Utility
It started to feel like SUBSTRING operation is the best way to go?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 100 total points
Comment Utility
Looks like some values are less than 23 characters long.

Try:
conv-from table
 select  top 1  convert(datetime, CounterDateTime) from (SELECT CounterDateTime FROM CounterData WHERE LEN(CounterDateTime) = 23) As myTable
0
 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Try:
select  top 1  convert(datetime, CounterDateTime) from CounterData WHERE CounterDateTime like ('____-__-__ __:__:__.___');
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"It started to feel like SUBSTRING operation is the best way to go?"

well maybe, but as I can see no data exceptions I'm in total darkness on the best way forward..........

have you tried my suggestion at all?

and can you share some of results where isdate() says the string isn't a date?)
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Use the style parameter and the ISDATE() function, e.g.

DECLARE @Sample TABLE ( DateColumn CHAR(24) );

INSERT  INTO @Sample
        ( DateColumn )
VALUES  ( '2014-03-05 17:03:18.518' );

SELECT  * ,
        CONVERT(DATETIME, DateColumn, 121)
FROM    @Sample
WHERE   ISDATE(DateColumn) = 1;

Open in new window

0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
You may find that your problem is just down to your local server collation settings. I'd try converting and specifying UTC formatting:
SELECT CONVERT(DATETIME, CounterDateTime, 120) 
     FROM CounterData

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
CAST will accept any valid datetime format.  Allow the entire string to be processed, and skip any rows that don't have a valid datetime.


SELECT CAST(CounterDateTime AS datetime) AS CounterDateTime, ...
FROM CounterData
WHERE
    ISDATE(CounterDateTime) = 1
0
 

Author Closing Comment

by:bozer
Comment Utility
Thanks to you both,

I decided to use it this way:
   select CounterDateTime,
   cast(SUBSTRING(CounterDateTime,1,10) as datetime)as TodaysDatePart,
   cast(SUBSTRING(CounterDateTime,12,8) as time(7))as TodaysTimePart
   from CounterData

The table I am using is auto populated by Windows PerfMon Data Collector so I am almost certain the column I am trying to convert will have valid data. (I am confused why they choose to create the column with char data type rather than datetime though)

Maybe I am hitting a SQL Server bug as I am not running on latest SR.

Thank you for your troubles.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now