none
Data type conversion issue RRS feed

  • Question

  • Hi guys
    I exported some data from a text file to sql server. Here is the sample data..

     

    This table has about 2 million rows.There is a date field in the table which comes as a 'nvarchar' in sql .When i try to convert it to a 'datetime' , i get an error as operation timed out..

     

    Here is the data from the text file...

    Date dispensed Outliers Formulation ID Provider Number (dispensing) NSS flag Patient category Units dispensed Total days supply
    1/01/2006 12:00:00 a.m. normal 106509.00 7952 I A 120.00 30.00
    1/01/2006 12:00:00 a.m. normal 106509.00 8208 I A 360.00 90.00
    1/01/2006 12:00:00 a.m. normal 106509.00 9460 I A 120.00 30.00
    1/01/2006 12:00:00 a.m. normal 106509.00 10184 I A 120.00 60.00
    1/01/2006 12:00:00 a.m. normal 106509.00 10291 I A 120.00 60.00
    1/01/2006 12:00:00 a.m. normal 106509.00 11149 I A 120.00 30.00
    1/01/2006 12:00:00 a.m. normal 106509.00 11294 I A 120.00 60.00
    1/01/2006 12:00:00 a.m. normal 106509.00 11777 I A 120.00 30.00
    1/01/2006 12:00:00 a.m. normal 106509.00 12048 I A 120.00 30.00

     

    I have tried the bulk insert as well.

    Here is the script for the create table ..

     

    USE [Library]

    GO

    /****** Object: Table [dbo].[tablename] Script Date: 10/03/2006 14:45:59 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[NormalOutlier1](

    [Datedispensed] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

    [Outliers] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

    [Formulation ID] [float] NULL,

    [Provider Number (dispensing)] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

    [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

    [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,

    [Units dispensed] [float] NULL,

    [Total days supply] [float] NULL

    ) ON [PRIMARY]

     

     

     

    Hope this helps

     

    Tuesday, October 3, 2006 2:38 AM

Answers

All replies