Data types - moving data to SQL Server

[deleted_user]

I have many SAS data files, that I need to move to SQL Server.
The date and time fields keep erroring out.
I am doing a simple
insert into table (fields)
select fields from SAS table
for the date fields I have tried put (dob, date10.) as dob - and that errors out with a Conversion Failed when converting date and /or time from character string.
Same thing with time fields - I tried put (collecttime, time8.) as time
The data in SAS are formatted as Number 5 YYMMDDN8
and Number 5 Time8.
The data I want in SQL Server 2008 would be a time(7) for the times and date for the dates.

Any recommendations?

[darrylovia]

To work around your issue, I would convert the sas dates to sas datetime using the dhms function and datetime21.2 format. Insert the new datetime field into SQL Server. Then use the SQL update statement to parse out the date from the time in SQL Server. This would be a pain but it should work.

[deleted_user]

I ended up doing a really crappy case statement:
case when dob <>. then put(dob, mmddyy10.) end as dob
this is really crappy syntax, but works!

Does anyone know how to do a Ltrim(rtrim( )) to get rid of extra spaces?

[Peter_C]

TRIM(LEFT( YOUR_STRING ))

[darrylovia]

see the STRIP function strip(mystring)