Posted by suslikovich on 03/06/06 19:28

Hi all,
I am getting this error when insert values from one table to another in
the first table the values are varchar (10). In the second they are
datetime. The format of the data is mm/dd/yyyy to be easily converted
to dates. The conversion in this case is implicit as indicated in SQL
Server documentation. Here is my query:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description
UNION
SELECT program_id + 100000, program_name, start_date, end_date,
campaign_id AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.programs
UNION
SELECT execution_id + 200000, execution_name, start_date,
end_date, program_id + 100000 AS ParentID, NULL AS ListID
FROM mysqlfactiva.dbo.executions
UNION
SELECT wave_id + 300000, wave_name, start_date, end_date,
mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL AS
ListID
FROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executions
WHERE mysqlfactiva.dbo.waves.execution_id =
mysqlfactiva.dbo.executions.execution_id

I am referencing programs table two times. If I just select this all I
get all data I need. When doing insert I get a message:

Server: Msg 242, Level 16, State 3, Line 1
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.

If I execute just first part of the query before first union, I insert
data fine:

INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,
mysqlfactiva.dbo.campaigns.campaign_name AS Name,
MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,

MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programs
WHERE mysqlfactiva.dbo.campaigns.campaign_id =
mysqlfactiva.dbo.programs.campaign_id
GROUP BY mysqlfactiva.dbo.campaigns.campaign_id,
mysqlfactiva.dbo.campaigns.campaign_name,
mysqlfactiva.dbo.campaigns.description


As soon as I use union I get the above error. This is very strange
since even when I execute the query using first union where the dates
come from the same table 'programs' I get the error. Why I can insert
from programs first time and can's second time?

Any help will be appreciated.

Thanks,
Stan

[Back to original message]