07-10-2014 09:26 AM
I am trying to append date value in sql server table but getting the below error :
"ERROR: CLI transact error: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when
converting date and/or time from character string"
In base sas table I have date value in yymmdd10. format (example - "1960-01-06" )
Below steps I am following to insert:
step 1 : connect SQL server through ODBC
libname utsql odbc dsn=stg_oiap pw='.....' user=.... BCP=YES;
connect to odbc as ecs (datasrc="stg_oiap"
execute ("truncate table INSTALMENTTYPE") by ecs;
disconnect from ecs;
Step 2 : Appending data to sql server base table( INSTALMENTTYPE )
proc append base=utSql.INSTALMENTTYPE
All the fields are appending except date column in sql server table.
When I connect sql server through sas using odbc , I can see that date field in sql server table shows in
SAS session as character in $10. format. (example - "2014-01-06" )
Please suggest or help me to resolve the same.
07-10-2014 09:50 AM
What is the format of the date variable in sas_table? I suspect it would be numeric, e.g. date9. or yymmdd10. Perhaps try one of the following:
apply the correct format to the variable, yymmdd10. and then re-try.
Alternatively, make a new character variable, and set it to put(date,yymmdd10.). Then drop the numeric.
07-10-2014 11:43 AM
You see a date in as human translating the string but in SAS it is numeric.
The quickest way is transforming the data variable in SAS to a string (length 10) and after that do the append.
All variables you are seeing should be of exactly the same type in the DBMS and in SAS.
Sometimes you can instruct the conversion by using dbtype/dbsastype options forcing a dedicated conversion.
07-14-2014 03:06 AM
Thank you for your suggestion.
But I have tried the same and again getting the same error.
legth ,format and informat of sas_table and sql server table are same and then i tried to append .
One strange thing I noticed,when i tried to insert values in SQL server table by below code,it inserted successfully.
insert into sql_table (date1 , date2) values ('1988-09-10' , '2012-08-14');
But when i try to insert from sas table with same value ,I am getting the same error.
please suggest .
12-09-2014 11:38 AM
If you check the datatypes on the SQL-Server as Jaap Karman wrote with:
Connect to ODBC (DSN=stg_oiap);
Create Table Info_Table As
Select * From Connection to ODBC
(ODBC:QLColumns (, , "INSTALMENTTYPE" ));
Disconnect From ODBC;
07-14-2014 03:34 AM
Subh, your posted sql is suggesting the variables date1 an date2 are string (text) fields as you are updating them with strings (text).
Your human interpretation can fool you on what is really stored. To check:
- what types are really there in SQL server?
The best thing to go for this is looking at this as a MS-SQL DBA. Data Types (micorosoft).
You will find the technical timestamp the datetime and many more. The technical approach is completely different as with SAS. There must conversion be done.
An old often practized habit for administration is coding dates as strings. Al lot of conversion-troubles as result remember the milo fear.
- What type are you using in SAS?
Remember the date reprensentation is one of integer numbers since 1 jan 1960, but a lot of conversions are possible.
Did you check that allready?.
This is the conversion SAS - SQL server SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Third Edition
See what happens:
- to a datetime formatted SAS-number. That is converted to a SQL-server timestap. You can see datestrings in SAS because of the format...
- a Date formatted SAS-numer. That is converted to a SQL-server date.
The DBType= overwrite option is mentioned.
The next thing is chedking needed conversions are done correctly.
There could be some nasty perception somewhere failing, knowing when that is happening a bypass is perhaps possible.
The analyses of the issue is not going beyond "it does not work" at this moment.
Computers are not human (yet) they are doing exactly what is instructed not likely what is intended. A cup of coffee?
07-14-2014 04:38 AM
I was not alert seeing your proc append and SQL-server.
The base-dataset is at the SQL server side and the updated is coming from SAS. This is a good approach for performance reasons as minimizing the datatransfer.
The technical effect is however more challenging as the update SAS dataset should go to the database using the options at that side. This is implicit pas through.
What is happening can be traces with sastrace SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Third Edition
The loading this way is often accompanied with "bulk loading" guess what: 50276 - Bulk loading dates fails when you use the SQL Server Native Client 10 ODBC driver
SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition because only native drivers SQl server exist for Windows.