Solved

Convert char(24) to datetime

Posted on 2014-04-10
13
565 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
[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
  • 4
  • 3
  • 3
  • +3
13 Comments
 
LVL 49

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 49

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 49

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
 
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 49

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 34

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:Scott Pletcher
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

624 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