Conversion error when importing data

Question

Conversion error when importing data

deep_kkumar

Hall of Fame

Points: 3243
More actions
May 19, 2012 at 11:22 am

#274312

Hi,
I am importing data from text file, where the input column value is like
"02-APR-12 12.49.24.938000 PM" and the destination table datatype for the column is specified as "datetime".
If i insert the value using convert i am getting this below error.Msg 241,
Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Please help me with this.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

deep_kkumar (5/19/2012)
Hi,
I am importing data from text file, where the input column value is like
"02-APR-12 12.49.24.938000 PM" and the destination table datatype for the column is specified as "datetime".
If i insert the value using convert i am getting this below error.Msg 241,
Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Please help me with this.
Thanks.

Hi

First, you should use at least the "datetime2" type (in sql server2008 and above)..

Then you should try some conversion. I would recommend the following link:

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

1. The format is of DATETIME2 and not DATETIME.

2. You have dots as the separator for the time component instead of colons. A valid date will look like : '02-APR-12 12:49:24.934 PM'

So for converting this, first you will have to change the data-type to datetime2, replace all dots with colons except for the last dots (which separates seconds and milliseconds).

Here are some ways of converting that string into a DATETIME data-type

DECLARE @time VARCHAR(100) = '02-APR-12 12.49.24.934000 PM';

-- DATETIME2 conversion

-- Assumptions

-- The string format will always be dd-mmm-yy hh.mm.ss.nnnnnn AM

SELECT @time = STUFF ( REPLACE(@time ,'.',':') ,19,1,'.') ;

SELECT CAST(@time AS DATETIME2) [Datetime2];

-- DATETIME conversion

-- Assumptions

-- The string format will always be dd-mmm-yy hh.mm.ss.nnnnnn AM

-- Reset to original value

SELECT @time = '02-APR-12 12.49.24.934000 PM';

SELECT @time = STUFF(STUFF ( REPLACE(@time ,'.',':'),19,1,'.') , 23, 3,'') ;

SELECT CAST(@time AS DATETIME) [Datetime];

Follow the comments for extra details.

deep_kkumar Hall of Fame Points: 3243 More actions · Answer 3

Hi Thanks for the reply.

So i cannot insert the value as PM and AM in the datatype column which is defined as "datetime" or Datetime2 in my destination table.

Please confirm.

If i remove the AM and PM from the value then the data wont be correct, so will you suggest to keep the datatype as varchar in the destination table.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

deep_kkumar (5/19/2012)
Hi Thanks for the reply.
So i cannot insert the value as PM and AM in the datatype column which is defined as "datetime" or Datetime2 in my destination table.
Please confirm.
If i remove the AM and PM from the value then the data wont be correct, so will you suggest to keep the datatype as varchar in the destination table.

Lets use your primer "02-APR-12 12.49.24.934000 PM"

You can transform it to "2012-04-02 12:49:24.934000 PM"

If you manage to extract the following variables

@date = "2012-04-02 12:49:24.934000" which is right format,

@AMorPM = {AM|PM}

and

@hour = the hour from @date

Then it is easy:

you can apply the following:

If @AMorPM = 'PM' And @hour < 12

Begin

Dateadd(Hour,12,@date)

End

If @AMorPM = 'AM' And @hour > 12

Begin

Dateadd(Hour,-12,@date)

End

...and finally in @date you'll have the right time without AM and PM

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com