inserting records

I am trying to insert records from one table to another and I get the
following error.
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
The statement has been terminated.
What do I need to do to get around this?
The statement I am using to insert the records is:
INSERT INTO TIME_DIM2
select DISTINCT
Date_occured_from AS FULL_DATE,
datepart(DW,Date_occured_from ) as DAY_OF_WEEK,
datepart(DD,Date_occured_from ) as DAY_OF_MONTH,
datepart(DY,Date_occured_from ) as DAY_OF_YEAR,
datepart(wk,Date_occured_from ) as WEEK_NUMBER,
datepart(MM,Date_occured_from ) as MONTH_NUMBER,
datepart(YY,Date_occured_from ) as YEAR_NUMBER,
Datename(month,Date_occured_from ) as MONTH_NAME,
datepart(MM,Date_occured_from ) as FISICAL_PERIOD_NO,
datepart(YY,Date_occured_from ) as FISICAL_YEAR,
CASE
WHEN datepart(MM,Date_occured_from )IN('1','2','3')THEN 'Q1'
WHEN datepart(MM,Date_occured_from )IN('4','5','6')THEN 'Q2'
WHEN datepart(MM,Date_occured_from )IN('7','8','9')THEN 'Q3'
WHEN datepart(MM,Date_occured_from )IN('10','11','12')THEN 'Q4'
end as QUARTER,
'MIS' as CREATED_BY,
GETDATE() as CREATED_DATE,
NULL as UPDATED_BY,
NULL as UPDATED_DATE
FROM bi..Allfile_up
The source table has the follwing structure
[Det_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[File] [numeric](18, 0) NULL ,
[Unit_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORI] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSR_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Details] [char] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_open] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ass_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Jur_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Member_out] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Diary_date] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_from] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Time_occured_from] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date_occured_to] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Time_occured_to] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Restriction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Location] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Extract_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[count] [numeric](18, 0) NULL
The destination table has the following structure
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[time_dim2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[time_dim2]
GO
[TIME_KY] [int] IDENTITY (1, 1) NOT NULL ,
[FULL_DATE] [smalldatetime] NOT NULL ,
[DAY_OF_WEEK] [smallint] NULL ,
[DAY_OF_MONTH] [smallint] NULL ,
[DAY_OF_YEAR] [smallint] NULL ,
[WEEK_NUMBER] [smallint] NULL ,
[MONTH_NUMBER] [smallint] NULL ,
[YEAR_NUMBER] [smallint] NULL ,
[MONTH_NAME] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FISICAL_PERIOD_NO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FISICAL_YEAR] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUARTER] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CREATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATED_DATE] [smalldatetime] NULL ,
[UPDATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UPDATED_DATE] [smalldatetime] NULL
ThanksSome date in the table is outside the range acceptable for a smalldatetime..
run the following and it will identify the bad recoreds:
Select Date_occured_from
FROM bi..Allfile_up
Where IsDate(Date_occured_from) = 0 Or
(IsDate(Date_occured_from) = 1 And
Date_occured_from Not Between '19000101' And '20790606')
"Munch" wrote:

> I am trying to insert records from one table to another and I get the
> following error.
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
> The statement has been terminated.
> What do I need to do to get around this?
>
> The statement I am using to insert the records is:
> INSERT INTO TIME_DIM2
> select DISTINCT
> Date_occured_from AS FULL_DATE,
> datepart(DW,Date_occured_from ) as DAY_OF_WEEK,
> datepart(DD,Date_occured_from ) as DAY_OF_MONTH,
> datepart(DY,Date_occured_from ) as DAY_OF_YEAR,
> datepart(wk,Date_occured_from ) as WEEK_NUMBER,
> datepart(MM,Date_occured_from ) as MONTH_NUMBER,
> datepart(YY,Date_occured_from ) as YEAR_NUMBER,
> Datename(month,Date_occured_from ) as MONTH_NAME,
> datepart(MM,Date_occured_from ) as FISICAL_PERIOD_NO,
> datepart(YY,Date_occured_from ) as FISICAL_YEAR,
> CASE
> WHEN datepart(MM,Date_occured_from )IN('1','2','3')THEN 'Q1'
> WHEN datepart(MM,Date_occured_from )IN('4','5','6')THEN 'Q2'
> WHEN datepart(MM,Date_occured_from )IN('7','8','9')THEN 'Q3'
> WHEN datepart(MM,Date_occured_from )IN('10','11','12')THEN 'Q4'
> end as QUARTER,
> 'MIS' as CREATED_BY,
> GETDATE() as CREATED_DATE,
> NULL as UPDATED_BY,
> NULL as UPDATED_DATE
> FROM bi..Allfile_up
>
> The source table has the follwing structure
> [Det_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [File] [numeric](18, 0) NULL ,
> [Unit_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_updated] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ORI] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OSR_code] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Details] [char] (240) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_open] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Ass_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Jur_coll] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Member_out] [char] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Diary_date] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_occured_from] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Time_occured_from] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Date_occured_to] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Time_occured_to] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Restriction] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Location] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extract_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [count] [numeric](18, 0) NULL
> The destination table has the following structure
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[time_dim2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[time_dim2]
> GO
> [TIME_KY] [int] IDENTITY (1, 1) NOT NULL ,
> [FULL_DATE] [smalldatetime] NOT NULL ,
> [DAY_OF_WEEK] [smallint] NULL ,
> [DAY_OF_MONTH] [smallint] NULL ,
> [DAY_OF_YEAR] [smallint] NULL ,
> [WEEK_NUMBER] [smallint] NULL ,
> [MONTH_NUMBER] [smallint] NULL ,
> [YEAR_NUMBER] [smallint] NULL ,
> [MONTH_NAME] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FISICAL_PERIOD_NO] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FISICAL_YEAR] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [QUARTER] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CREATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CREATED_DATE] [smalldatetime] NULL ,
> [UPDATED_BY] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UPDATED_DATE] [smalldatetime] NULL
>
> Thanks