Datetime convert fails!

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:4
5: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_Genera
l_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 helpsAssume that the data is imported into their respective table columns
correctly, you can change a.m. to am and the convertion to datetime should
work.
Linchi
"mita" wrote:

> Hi guys
> I exported some data from a text file to sql server. Here is the sample da
ta..
>
> This table has about 2 million rows.There is a date field in the table whi
ch
> 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 fl
ag
> 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_Gene
ral_CI_AS
> NULL,
> [NSS flag] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
> [Patient category] [nvarchar](max) COLLATE Latin1_General_CI_AS NU
LL,
> [Units dispensed] [float] NULL,
> [Total days supply] [float] NULL
> ) ON [PRIMARY]
>
>
>
> Hope this helps
>