Out of range datetime value error when inserting using select...union

[suslikovich]

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.db o.campaigns.cam paign_id AS CampaignID,
mysqlfactiva.db o.campaigns.cam paign_name AS Name,
MIN(mysqlfactiv a.dbo.programs. start_date) AS DateStart,

MIN(mysqlfactiv a.dbo.programs. end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.db o.campaigns, mysqlfactiva.db o.programs
WHERE mysqlfactiva.db o.campaigns.cam paign_id =
mysqlfactiva.db o.programs.camp aign_id
GROUP BY mysqlfactiva.db o.campaigns.cam paign_id,
mysqlfactiva.db o.campaigns.cam paign_name,
mysqlfactiva.db o.campaigns.des cription
UNION
SELECT program_id + 100000, program_name, start_date, end_date,
campaign_id AS ParentID, NULL AS ListID
FROM mysqlfactiva.db o.programs
UNION
SELECT execution_id + 200000, execution_name, start_date,
end_date, program_id + 100000 AS ParentID, NULL AS ListID
FROM mysqlfactiva.db o.executions
UNION
SELECT wave_id + 300000, wave_name, start_date, end_date,
mysqlfactiva.db o.waves.executi on_id + 200000 AS ParentID, NULL AS
ListID
FROM mysqlfactiva.db o.waves, mysqlfactiva.db o.executions
WHERE mysqlfactiva.db o.waves.executi on_id =
mysqlfactiva.db o.executions.ex ecution_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.db o.campaigns.cam paign_id AS CampaignID,
mysqlfactiva.db o.campaigns.cam paign_name AS Name,
MIN(mysqlfactiv a.dbo.programs. start_date) AS DateStart,

MIN(mysqlfactiv a.dbo.programs. end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.db o.campaigns, mysqlfactiva.db o.programs
WHERE mysqlfactiva.db o.campaigns.cam paign_id =
mysqlfactiva.db o.programs.camp aign_id
GROUP BY mysqlfactiva.db o.campaigns.cam paign_id,
mysqlfactiva.db o.campaigns.cam paign_name,
mysqlfactiva.db o.campaigns.des cription
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

[Erland Sommarskog]

(su*********@ya hoo.com) writes:

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:
Note that the interpretation is dependent on the settings for DATEFORMAT
and LANGUAGE.
INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,
ListID)
SELECT mysqlfactiva.db o.campaigns.cam paign_id AS CampaignID,
mysqlfactiva.db o.campaigns.cam paign_name AS Name,
MIN(mysqlfactiv a.dbo.programs. start_date) AS DateStart,

MIN(mysqlfactiv a.dbo.programs. end_date) AS DateEnd,
NULL AS ParentID,
NULL AS ListID
FROM mysqlfactiva.db o.campaigns, mysqlfactiva.db o.programs
So the data type of mysqlfactiva.db o.programs.star t_date is varchar(10)?

Excuse me, if I am nosy, but a MIN on that columns appears somewhat
funny to me. If there is a date in early January you will use that
date, no matter which year it is in?

Better would be

MIN (CASE WHEN isdate(start_da te) = 1 THEN start_date END
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?

In the first query, you are only taking the first value, in the
second query you are taking all the values. This query should give
you the bad data:

SELECT * FROM mysqlfactiva.db o.programs.star t_date
WHERE isdate(start_da te) = 0 OR isdate(end_date ) = 0

By the way, I seem recall that MySQL has a very liberal view on what
is a good date...
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx