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.
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.