Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

Convert char(24) to datetime

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
bozer
Asked:
bozer
  • 4
  • 3
  • 3
  • +3
3 Solutions
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
hnasrCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
bozerAuthor Commented:
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
 
PortletPaulCommented:
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
 
bozerAuthor Commented:
It started to feel like SUBSTRING operation is the best way to go?
0
 
hnasrCommented:
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
 
hnasrCommented:
Try:
select  top 1  convert(datetime, CounterDateTime) from CounterData WHERE CounterDateTime like ('____-__-__ __:__:__.___');
0
 
PortletPaulCommented:
>>"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
 
ste5anSenior DeveloperCommented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
bozerAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 4
  • 3
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now