Inserting records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting records

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-21 : 11:31:43

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

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-21 : 11:33:37

For a start

You need to supply the column list for the INSERT to make sure you're putting the correct data in the right columns....

Brett

8-)

EDIT: Secondly, I'm suprised you didn't get he Can't insert into an indentity column error first...or a mismatched number of columns error

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-21 : 11:43:05

Its not necessary to put the column list in the insert statement.

DO you know why I am getting this error?

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2005-04-21 : 11:56:53

quote:
Originally posted by mdhingra01

Its not necessary to put the column list in the insert statement.

DO you know why I am getting this error?

smalldatetime stores dates with accuracy to the minute, datetime on the other hand stores dates with accuracy of one three-hundreth of a second. I think you need to check your Date_occured_from field. You might want to change the type to datetime instead of smalldatetime. I mean is storage space a major issue?

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destruction

mdhingra01
Posting Yak Master

179 Posts

Posted - 2005-04-21 : 12:09:04

space is not an issue and right now date_occured from is defined as a char(8) field and ints going into full_date which is defined as smalldatetime. Are you suggesting that I change ful_date to datetime?

I'll try that.

Hey you are brilliant!!!..... That did it. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources