inserting the date into SQL Server problem

Im having problems inserting the current date into SQL server from my ASP.net application.

The error that asp.net throws is:

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated. "

In my page, im setting the date as follows:

Dim strDate as Date

strDate = now()

Then in my page I call a function called "do_store"

This works fin putting my variables into my database until I tried adding the date to the statement. Here is my function. Can anyone see my problem?

Public Function do_store(ByVal strTestID As String, ByVal strUserName As String, ByVal strUserID As String, ByVal strWrong As String, ByVal strRight As String, ByVal strAnswers As String, ByVal strDate As Date)
Dim strSQL As String
Dim sConn As String
Dim oConn As SqlConnection
'do the insert into the database
strSQL = "insert into test_results (test_plan_id, user_name, user_id, questions_wrong, questions_right, questions_answered, date_time) values (" & strTestID & ", '" & strUserName & "', " & strUserID & ", " & strWrong & ", " & strRight & ", " & strAnswers & ", '" & strDate & "')"
sConn = ConfigurationSettings.AppSettings("ConnectionString")
oConn = New SqlConnection(sConn)
Dim myCommand As SqlCommand

myCommand = New SqlCommand(strSQL, oConn)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
End Function::Im having problems inserting the current date into SQL server from my ASP.net
::application.
::
::The error that asp.net throws is:
::
::"The conversion of a char data type to a datetime data type resulted in an out-of-range
::datetime value. The statement has been terminated. "

* Your datetime formates between the server and your process dont match.

Means: your server expects (for example) YMD, and you deliver YDM. Result: the date gets interpreted as month, and this can result in you "entering" month values from 13-31, which ARE out of range.

You need to make sure that the string you create actually is what the SQL Server expects.

BTW - if this is for a website, your code is insecure and very sucesseptable to a SQL INJECTION ATTACK.

Please read up the fundamental basics of SQL security - do it now.|||How do I check what format SQL Server wants?
how can I format the date so it fits into SQL server?
I got it all running locally (SQL Server and VS.NET) so Presumed their locale setting would be the same.
Im only building this as an intranet so its not open to the big bad world.|||This is explained in the SQL Server oducmentaiton. Actually you should not care - you should ALAWYS insert datetimes as ISO form, country independant. The correct way to insert this is:

return String.Format ("'{0:yyyy-MM-dd HH:mm:ss}'", Value);

At least this is what our EntityBroker O/R mapping framework uses, and it works so far.

The documentation of SQL Server has more information on this.

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

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
ThanksMunch,
What format are you using to represent the date value in Date_occured_from?.
Smalldatetime data type range is 19000101 through 20790606.
AMB
"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|||Some 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