Solved

Convert char(24) to datetime

Posted on 2014-04-10
13
538 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
ID: 39993237
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
ID: 39993240
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
ID: 39993251
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
ID: 39993264
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
ID: 39993273
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
ID: 39993274
It started to feel like SUBSTRING operation is the best way to go?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 100 total points
ID: 39993276
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
ID: 39993279
Try:
select  top 1  convert(datetime, CounterDateTime) from CounterData WHERE CounterDateTime like ('____-__-__ __:__:__.___');
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39993285
>>"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 33

Expert Comment

by:ste5an
ID: 39993527
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
ID: 39993717
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
ID: 39994914
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
ID: 40001152
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 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

11 Experts available now in Live!

Get 1:1 Help Now