update datetime column

[JEM]

I am trying to update a datetime column in one table to the value in
another table. Both columns are datetime (8) but I keep getting the
error message: Your entry cannot be converted to a valid date time
value. I even tried using the Convert function, but get the same
problem. Any ideas?

Here is my sql without Convert and with:

UPDATE dbo.tblEnrollment
SET Transaction_Date = SELECT dbo.Enroll.TransactionDate FROM
dbo.Enroll WHERE dbo.Enroll.VehicleVIN = dbo.tblEnrollment.
[Vehicle_Vin]

UPDATE dbo.tblEnrollment
SET Transaction_Date = CONVERT(DATETIME, SELECT
dbo.Enroll.TransactionDate FROM dbo.Enroll WHERE dbo.Enroll.VehicleVIN
= dbo.tblEnrollment.[Vehicle_Vin],102)

Thanks,
Jenn

 

[Sylvain Lafontaine]

The problem here is not with the datetime field itself but with the update
syntaxe. When using things like « SET Transaction_Date = SELECT ... », the
Select statement must return a single scalar value. Try something like:

UPDATE dbo.tblEnrollment
SET Transaction_Date = dbo.Enroll.TransactionDate
FROM dbo.Enroll inner join dbo.tblEnrollment on dbo.Enroll.VehicleVIN =
dbo.tblEnrollment.Vehicle_Vin

As always, using table aliases will help you and finally, using different
names like VehicleVIN and Vehicle_VIN is a good recipe for confusion later.

 

[JEM]

Thanks - that solved it!

Yes, I agree. I inherited the problem and it's a one time deal so I
didn't bother with renaming objects.

Jenn

The problem here is not with the datetime field itself but with the update
syntaxe.  When using things like « SET Transaction_Date = SELECT ...», the
Select statement must return a single scalar value.  Try something like:

UPDATE  dbo.tblEnrollment
SET Transaction_Date = dbo.Enroll.TransactionDate
FROM dbo.Enroll inner join dbo.tblEnrollment on dbo.Enroll.VehicleVIN =
dbo.tblEnrollment.Vehicle_Vin

As always, using table aliases will help you and finally, using different
names like VehicleVIN and Vehicle_VIN is a good recipe for confusion later..

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I am trying to update a datetime column in one table to the value in
another table. Both columns are datetime (8) but I keep getting the
error message:  Your entry cannot be converted to a valid date time
value. I even tried using the Convert function, but get the same
problem. Any ideas?

Here is my sql without Convert and with:

UPDATE    dbo.tblEnrollment
SET Transaction_Date = SELECT dbo.Enroll.TransactionDate FROM
dbo.Enroll WHERE dbo.Enroll.VehicleVIN = dbo.tblEnrollment.
[Vehicle_Vin]

UPDATE    dbo.tblEnrollment
SET Transaction_Date = CONVERT(DATETIME, SELECT
dbo.Enroll.TransactionDate FROM dbo.Enroll WHERE dbo.Enroll.VehicleVIN
= dbo.tblEnrollment.[Vehicle_Vin],102)

Thanks,
Jenn- Hide quoted text -

- Show quoted text -