Date field not appending in sql server 2008 database

Reply
Occasional Contributor
Posts: 7

Date field not appending in sql server 2008 database

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;

          proc sql;

            connect to odbc as ecs (datasrc="stg_oiap"

               pw="...." user=....);

               execute ("truncate table  INSTALMENTTYPE") by ecs;

          disconnect from ecs;

          quit;

Step 2 : Appending data to sql server base table( INSTALMENTTYPE )

          proc append base=utSql.INSTALMENTTYPE

                   data=sas_table force;

            run;

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.

Super User
Super User
Posts: 7,979

Re: Date field not appending in sql server 2008 database

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.

Occasional Contributor
Posts: 7

Re: Date field not appending in sql server 2008 database

Thank you for your reply.

I have tried the same but still the same error I am getting.

Trusted Advisor
Posts: 3,215

Re: Date field not appending in sql server 2008 database

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. 

---->-- ja karman --<-----
Occasional Contributor
Posts: 7

Re: Date field not appending in sql server 2008 database

Hi Jaap,

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.

proc sql;

insert into sql_table (date1 , date2) values ('1988-09-10' , '2012-08-14');

quit;

But when i try to insert from sas table with same value ,I am getting the same error.

please suggest .

Super Contributor
Posts: 340

Re: Date field not appending in sql server 2008 database

If you check the datatypes on the SQL-Server as Jaap Karman wrote with:

Proc SQL;

  Connect to ODBC (DSN=stg_oiap);

  Create Table Info_Table As

  Select * From Connection to ODBC

    (ODBC:Smiley FrustratedQLColumns (, , "INSTALMENTTYPE" ));

  Disconnect From ODBC;

Quit;

?

Trusted Advisor
Posts: 3,215

Re: Date field not appending in sql server 2008 database

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?


    

---->-- ja karman --<-----
Trusted Advisor
Posts: 3,215

Re: Date field not appending in sql server 2008 database

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.

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 7 replies
  • 1681 views
  • 0 likes
  • 4 in conversation