Solved

Convert char(24) to datetime

Posted on 2014-04-10
13
552 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

837 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