Post reply

Converting from nvarchar(50) to datetime

  • 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.

  • December 20, 2005 at 8:12 am

    #610590

    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)

     

  • December 20, 2005 at 8:49 am

    #610605

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

  • December 20, 2005 at 2:06 pm

    #610726

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

  • December 20, 2005 at 5:19 pm

    #610752

    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 

  • December 21, 2005 at 12:26 pm

    #610933

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

    Cheers,

    david russell

  • December 21, 2005 at 1:00 pm

    #610942

    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

  • December 21, 2005 at 1:05 pm

    #610946

    it's in mm/dd/yyyy format

  • December 22, 2005 at 11:46 am

    #611204

    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

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

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