Converting from nvarchar(50) to datetime

Question

Converting from nvarchar(50) to datetime

yaip

SSCrazy

Points: 2656
More actions
December 19, 2005 at 12:01 pm

#166823

I am converting data. I am using import/export wizard to get the data into the table.
I have a date field (dob) as nvarchar(50) in the source table and as datetime in the destination table. When I do it thru' import/export wiz, it gives me an error saying data may be lost, import/export failed. Does the date in the source table have to be in specific format? currently it is in mm/dd/yyyy format.

Viewing 9 posts - 1 through 8 (of 8 total)

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

peterhe SSChampion Points: 11469 More actions · Answer 1

Since the date in the source table is in the specific format, you have to tell SQL sever when you convert it to datetime like this:

convert (datetime,dob,101)

yaip SSCrazy Points: 2656 More actions · Answer 2

but what if i am not using any statements and using the import/export wizard?

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 3

You need to add a field to the table or make a view to pull from

Bill Scott SSCrazy Points: 2338 More actions · Answer 4

In general, whenever i have to do some kind of transformation on the data being imported, i will import it as is to a temporary table and then run a procedure that makes the transformations happen and put the results into a new table or insert them into an existing table.

The procedure can do all kinds of field validation and produce a log of the errors as well as transforming from one data type to another in a highly controlled manner. Unless you are using a very smart import tool, I have found this method is easiest to implement and maintain.

Bill

david russell-253790 SSCarpal Tunnel Points: 4042 More actions · Answer 5

If you are using SSIS you can do the convert on the select statement. that's pretty smart

Cheers,

david russell

SQLBill SSC Guru Points: 51440 More actions · Answer 6

I believe you are getting that message because the two fields are of different size. Your NVARCHAR is 50 characters but DATETIME is only 8 bytes. SQL Server doesn't know the length of the data in your NVARCHAR column, it only knows that it COULD be 50 characters and that won't fit in the DATETIME column - so it MIGHT have to truncate data. Might not, but it doesn't know so you get that message.

What does the data look like in your NVARCHAR column? If you run ISDATE() against it, does all the values equate to date/times?

-SQLBill

yaip SSCrazy Points: 2656 More actions · Answer 7

yaip

SSCrazy

Points: 2656

December 21, 2005 at 1:05 pm

#610946

it's in mm/dd/yyyy format

SQLBill SSC Guru Points: 51440 More actions · Answer 8

I wouldn't worry about the message then. Like I said, SQL Server creates that message based on the size (length) of the column. It compares the NVARCHAR(50) to the length of DATETIME and NVARCHAR(50) is larger...so data MIGHT be truncated. It doesn't compare the actual length of the data within the column.

-SQLBill