Extracting date & time from a nvarchar field

have u try this
SELECT CAST('2000-05-08 12:35:29.998' AS smalldatetime)

Got this:

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type smalldatetime.

Howdy
Having gone through your word doc i think you may have a couple of issues to address. With having a smalldate date and a small date time for the same date for each row you are storing the same data three times, once as the varchar and twice as a small date. You cannot use the small date as time only or date only. Date and Time data types are not introduced until sql 2008.

To get the data formated as you want the following query should work

SELECT
convert(varchar, cast('01/10/10 01:00:04' as smalldatetime),103),
convert(varchar, cast('01/10/10 01:00:04' as smalldatetime),108)

So I would store the date as a datetime type and then format as required, if formating is required for reporting I would consider a view with date and time formated as per the query above.

Hope that helps

@cyberkiwi had high hopes for this, but I got this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

I tried it for every single row you listed, in fact I have even verified it on SQL Server 2000/8.
Do you have any other formats of data that is not as shown?

Maybe it is your language setting..

Maybe, for the first one, use

update dataset set eventdate = convert(datetime, nullif(left(eventdatetime,8),''),1)

The last 1 or 3 refer to whether it is dd/mm/yy (3) or mm/dd/yy (1)

It might be. This bloody thing is trying my patience. Let me use the upsizing wizard from access to sql rather than the import/export in ss. see if that makes any diference. give me a minute.

select * from dataset
where len(eventdatetime) between 1 and 19

-- non conformists

ok I ran the last one and i got this:

1      Transaction Time      NULL      NULL      Area Num

What do you know... that looks like some HEADER row...
Just delete it before even starting the updates.  try http:#a34235815 again, but use 1 or 3 for the first query depending on whether 1/10 is 1-Oct or 10-Jan

code spot on. my mistake. 5 lines of rubbish crept into the file.
much obliged.