title: INSERT null datetime value into SQL table
body: <p>Below is a stored procedure we use are using to populate a table in an SQL database. The @expDate is a parameter that is sent in from a C# batch job, which gets it's values from and Excel spreadsheet. </p>
  
  <p>If the training for the current agent is active, the spreadsheet will not have a value in the expiration date cell. This causes a blank string to be sent to the stored procedure from the C# code, which then gets translated into the datetime value 1900-01-01. This is fine up until the point where I need to insert new records into the table using the cursor. </p>
  
  <p>If the @expDate value is 1900-01-01, then I need the value in the SQL record's expiration date field to be NULL. However, I cannot set @expDate = NULL nor can I simply set it to blank ('') [EDIT: I cannot set to blank due to a business rule. A blank value will cause the field's value to be set at 1900-01-01]. Any suggestions? </p>
  
  <pre><code>    DECLARE @date datetime = GETDATE()
      IF @expDate = '1900-01-01' SET @flag = '1'
      IF @expDate = '1900-01-01' SET @expDate = GETDATE()
  
      DECLARE prod_cursor CURSOR LOCAL SCROLL FOR
      SELECT ProductCode 
      FROM CourseToProduct 
      WHERE CourseCode = @courseCode and (TerminationDate &gt;= @expDate OR TerminationDate IS NULL)
  
      IF @flag = '1' SET @expDate = ''
  
      OPEN prod_cursor
  
      FETCH NEXT FROM prod_cursor
      INTO @productCode
  
      WHILE @@FETCH_STATUS = 0
      BEGIN
          IF NOT EXISTS
          (
          SELECT *
          FROM AgentProductTraining
          WHERE @sNumber = SNumber and 
                @courseCode = CourseCode and 
                @productCode = ProductCode and 
                @dateTaken = DateTaken
          )
          BEGIN
              IF @sNumber IS NOT NULL
              BEGIN
                  INSERT INTO AgentProductTraining
                              (
                               SNumber,
                               CourseCode,
                               ProductCode,
                               DateTaken,
                               DateExpired,
                               LastChangeOperator,
                               LastChangeDate
                              ) 
                  VALUES      (
                               @sNumber,
                               @courseCode,
                               @productCode,
                               @dateTaken,
                               COALESCE(@expDate, 'NULL'),
                               @lastChangeOperator,
                               @lastChangeDate
                             )    
              END
          END
  
          FETCH NEXT FROM prod_cursor
          INTO @productCode
      END
      CLOSE prod_cursor;
      DEALLOCATE prod_cursor;
  </code></pre>