Hi,

I've been having this problem. I'm trying to export a
series of date and time from ms access to sql server but
can only manage to export the date but not the time as the
date and time are two separate columns. The error that I
receive after execution in sql server is that there is
an "invalid character value for cast specification".

Please help, thanks.

Oh Dear

This comes up an awful lot not just from Access but nearly every system you
will encounter has different ranges of dates it allows and they will
eventually come unstuck

So you hold/Access holds dates and times in two different attributes. We
are going to have to concatenate them


declare @dt varchar(50)
declare @tm varchar(50)

set @dt = '12/10/2003'
set @tm = '13:00'

select CAST(@dt + ' ' + @tm as datetime)


I have always found SQL Server to be most amenable to dates in this format
(YYYYMMDD)


select CAST('20030304 13:00' as datetime)



Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"jayne" <anonymous (AT) discussions (DOT) microsoft.com> wrote