|
|
Posted by Erland Sommarskog on 03/08/06 01:11
(suslikovich@yahoo.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.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
So the data type of mysqlfactiva.dbo.programs.start_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_date) = 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.dbo.programs.start_date
WHERE isdate(start_date) = 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|