sql server new users: I'm using Access as a front end and trying to convert a string like 20070213
to a smalldate field.

Here's the access code I'm using:

UPDATE TWDLIN SET TWDLIN.BOLD = "#" & Mid([twdlin].[lestdp],5,2) & "/" &
Right([twdlin].[lestdp],2) & "/" & Left([twdlin].[lestdp],4) & "#"
WHERE (((TWDLIN.LBOL)="oolu3002344129"));

It's giving an error of a type conversion error.

What do I need to change?

Thanks for your help.

This looks like MS Access SQL. I am assuming this is against Linked SQL
Server tables?

If so, you could just use a pass-through query.

UPDATE t1 WITH(UPDLOCK)

SET
t1.BOLD =
"#" +
CONVERT(CHAR(10),
CONVERT(DATETIME, t1.lestdp, 112),
101)
+ "#"

FROM
dbo.TWDLIN AS t1

WHERE
t1.LBOL = 'oolu3002344129';

I think your problem is that your are trying to convert the STRING
"mm/dd/yyyy" into an Access DATE/TIME type (the "#'s" you are appending).
That's not how Access stores DATE/TIME.

MS Access DATA/TIME is a DOUBLE data type, where the Integer Part is the
days from 1/1/1900 and the Fractional Part is the fraction of a day, in
time, from midnight.

When you want to update it, update it with a string and allow the DB engine
to implicitly convert it.

If you are updating a SQL Server table, SQL Server DATETIME is much more
complicated. But, again, update it with a string and allow the DB engine to
implicitly convert it.

You could use the SQL Server T-SQL syntax, but MS Access is going to have a
problem with it; so, if you use a pass-through query instead:

UPDATE t1 WITH(UPDLOCK)

SET
t1.BOLD =
CONVERT(DATETIME, t1.lestdp, 112) -- Code 112 is the ISO format
'yyyymmdd'.

-- Code 101 is the U.S. 'mm/dd/yyyy' format.

FROM
dbo.TWDLIN AS t1

WHERE
t1.LBOL = 'oolu3002344129';


It is more simple.

Sincerely,


Anthony Thomas


--

[quoted text, click to view]