title: Writing SAS dates to SQL Server databse
body: <p>How to write SAS dates to Microsoft SQL Server 2016 <code>Date</code> data type in database?</p>
  
  <p>I got SAS data with a sas date <code>DataEndDay</code> and I want to write that into a database. The following bit is in use (buffer is just to speed up the testing-failing) : </p>
  
  <pre><code>libname valu oledb provider=sqloledb schema="dbo" INSERTBUFF=100
          properties=("User ID"="&amp;username." Password="&amp;pw."
                      "data source" = &amp;database. 
                      "initial catalog"=&amp;catalog.);
  
  proc sql noprint;
   insert into valu.Data_upload_from_me
  (   &lt;some_columns...&gt;, 
     &lt;more-columns...&gt;
  ,DataEndDay
  )
  
  select 
  &lt;some_columns_source...&gt;,
  &lt;more-columns_source...&gt;
  ,DataEndDay 
  from work.SAS_data_to_publish 
  ;quit;
  </code></pre>
  
  <p>Of course because SAS dates are numbers, direct writing is going to fail. What works is if I hard-code this as: </p>
  
  <pre><code>select 
  &lt;some_columns_source...&gt;,
  &lt;more-columns_source...&gt;
  ,'2018-12-12' 
  from work.SAS_data_to_publish 
  ;quit;
  </code></pre>
  
  <p>But If I convert the SAS date to string in SAS datasteps: </p>
  
  <pre><code>data SAS_data_to_publish ; 
      set SAS_data_to_publish ;
       dataEndday0 = put(DataEndDay, yymmddd10.);
       DataEndDay1 = quote(dataEndday0, "'") ;
  run;
  </code></pre>
  
  <p>and try to write either of these, I get conversion error: </p>
  
  <blockquote>
    <p>ERROR: ICommand::Execute failed. : Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <p>When I select the string it looks pretty ok: </p>
  
  <pre><code>proc sql; select DataEndDay1 from SAS_data_to_publish; quit;
  </code></pre>
  
  <blockquote>
    <p>'2018-12-12'</p>
  </blockquote>
  
  <p>previously I've managed to write  dateTimes with similar trick, which works: </p>
  
  <pre><code>proc format;
      picture sjm
      . = .
      other='%Y-%0m-%0d %0H:%0M:%0S:000' (datatype=datetime)
  ;run;
  
  data to_be_written; 
      set save.raw_data_to_be_written;
      DataEndDay0 = put(dhms(DataEndDay,0,0,0), sjm. -L);
  run;
  </code></pre>
  
  <p>Anyone ran into similar issues? How could I write the dates? 
  I could ask them to change the column to dateTime, maybe.... </p>
  
  <p>Thank you in advance. </p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I managed to develop a work-around, which works but is ugly and -frankly- I don't like it. It so happens that my date is same for all rows, so I can assing it to macro variable  and then use it in database writing. </p>
  
  <pre><code>data _NULL_; 
    set SAS_data_to_publish; 
    call symput('foobar', quote( put (DataEndDay , yymmddd10. -L), "'") ) ; 
  run;
  
  ....
  select 
    &lt;some_columns_source...&gt;,
    &lt;more-columns_source...&gt;
   ,&amp;foobar.
  from work.SAS_data_to_publish 
  ;quit;
  </code></pre>
  
  <p>Of course this would fail immediately should <code>DataEndDay</code> vary, but maybe demonstrates that something is off in <code>Proc SQL</code>s select clause....</p>
  
  <p><strong>Edit Edit</strong> Pasted the question to <a href="https://communities.sas.com/t5/New-SAS-User/Writing-SAS-dates-to-SQL-Server-databse/m-p/522200" rel="nofollow noreferrer">SAS forums</a></p>