Data Conversion Problem

I have tried the following in all kinds of combinations but cannot get it to
work. At this point I am not seeing the problem straight and need new eyes
to guide me.
CREATE TABLE EmpEvals
(
last_name varchar(25),
first_name varchar(25),
begin_dt datetime,
adj_beg_dt datetime,
termination_date datetime,
eval_months int
)
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
I am getting an error going from datetime to int with the CONVERT/CAST in
the SELECT statement. I've tried both CONVERT and CAST and just cannot get
the CONVERT/CAST the way SQL Server wants it. I even tried changing
eval_months to datetime and it complained. Someone PLEASE help before I have
no hair left to pull out!
TIA
MikeOn Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
wrote:

>INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
>termination_date, eval_months )
>SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
>last_name, first_name, begin_dt, adj_beg_dt, termination_date
>FROM employee
The order of the column list of the INSERT must match the order of the
column list of the SELECT. In the code above, last_name is getting
the data for eval_months, first_name is getting last_name, etc.
Roy Harvey
Beacon Falls, CT|||Hi Mike
DATEDIFF returns an INT so there should be no problem with conversion. In
fact, you shouldn't even need the CAST at all.
I think the problem is that you are returning the value for eval_ months
first in your select, but it is the last column in the table. So all your
select values are trying to go into the wrong columns, and there are
conversion errors.
Using eval_months in your SELECT only gives a column header to the result,
it does not map it to a column of the table you are inserting into. You cold
give it any column name you want and it would be ignored, since you are
inserting into a table, and not returning the SELECT result to the client.
Try this:
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT last_name, first_name, begin_dt, adj_beg_dt, termination_date,
DATEDIFF(m, begin_dt, GETDATE() )
FROM employee
HTH
Kalen Delaney, SQL Server MVP
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||Mike
How about to move an eval_months column at the beginning of the columns
list?
INSERT INTO EmpEvals (eval_months ,last_name, first_name, begin_dt,
adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||BLESS YOU!! That worked nicely. I need a vacation. I'm getting by
facts.
Thanks.
Mike
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:urs4a21tmcnc7kj680mk4vjbp4ip1d7bfb@.
4ax.com...
> On Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
> wrote:
>
> The order of the column list of the INSERT must match the order of the
> column list of the SELECT. In the code above, last_name is getting
> the data for eval_months, first_name is getting last_name, etc.
> Roy Harvey
> Beacon Falls, CT