title: How to implement SET_DATE_PART in SQL SERVER
body: <p>I want to implement SET_DATE_PART function of Informatica which sets part of a date column to particular value in SQL Server. I have a expression upon which I am trying to implement any function similar to <code>SET_DATE_PART</code> function of Informatica. This expression</p>
  <pre class="lang-sql prettyprint-override"><code>case 
  when ISDATE(SUBSTRING(COLUMN,1,7))=1 and CAST(SUBSTRING(COLUMN,1,2) AS INTEGER)&lt;=7 then 
  DATEADD(MM,-1,CONVERT(DATE,SUBSTRING(COLUMN,1,7),106)) 
  ELSE CONVERT(DATE,SUBSTRING(COLUMN,1,7),106)
  END AS TRANSFORMED  FROM TABLE
  ORDER BY 1
  </code></pre>
  <p>is working fine for me and returning date values in <code>YYYY-MM-DD</code> format. Now I want to set value coming in Date part of my expression to 01. I have tried lots of functions of SQL server like DATEPART,DATEADDbut nothing is working</p>