SQL Transaction uncommittable while using try..catch.. Why?

Looks like the 'commit transaction' is never reached because the code jumps to the catch block. To Avoid this you can add a 'rollback transaction' to your catch block like so:

alter procedure [datetransaction1] 
as
begin
    begin try
        begin transaction
        declare @a datetime
        exec datetransaction2 '2013-02-02 22:21', @a output
        select @a
        exec datetransaction2 '2013-020222:22', @a output
        select @a
        exec datetransaction2 '2013-02-02 22:23', @a output
        select @a

        commit transaction
    end try
    begin catch
        print 'Catch'
         rollback transaction
    end catch
end

The reason is: SQL Server dooms the transaction WHENEVER an error occurs, whatever the error is, whether it is in a TRY block or not, whether you saved a transaction state or not, whether the error occurs in a procedure or not, whatever you do.

When the error occurs in one of the procedure calls, the transaction is doomed. You can only rollback it completely (any savepoint will not help).

At the end, since the transaction is doomed, you cannot commit it...

Try this:

SET XACT_ABORT OFF -- pityful attempt to avoid the doom
BEGIN TRANSACTION
--
-- some useful TSQL instructions could be here
--
SAVE TRANSACTION SQL_SERVER_IS_GARBAGE -- another pityful attempt to do a partial restore
PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
BEGIN TRY
  DECLARE @n int
  SELECT @n = CONVERT(int,'ABC') -- some very benign data error here (example)
  COMMIT TRANSACTION -- will never reach here
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
  PRINT 'XACT_STATE='+CONVERT(varchar(10),XACT_STATE())
  IF XACT_STATE()=-1 BEGIN
    PRINT 'The transaction is doomed, say thanks to Sql Server!'
    PRINT 'CANNOT restore to the save point!'
    -- You can just cry here and abort all, you lost all the useful work
    ROLLBACK TRANSACTION
  END
  ELSE BEGIN
    -- would restore before the error if the transaction was not doomed
    ROLLBACK TRANSACTION SQL_SERVER_IS_GARBAGE -- will never reach here either!
  END  
END CATCH  

In short: a catch statement often causes a rollback (see 1). This depends on XACT_ABORT. Next, rollbacks are not contained to the SP where they are initiated (see 2).

The first reference (1) gives a workaround using @@trancount, see the accepted answer there.

Since the second call to datetransaction2 function caused severity level 16 error SQL Server automatically rolled back your transaction. That's the reason for the error you are seeing.

Here is a really nice article why the transactions gets into doomed state when severity level 16 error occurs.

To verify that its getting rolled back automatically I added the following line to your datetransaction2 proc : print XACT_STATE()

  create procedure [dbo].[datetransaction2] @text nvarchar(100), @res datetime OUTPUT  
  AS
  BEGIN 
     print 'Start'
      print XACT_STATE() 
      BEGIN TRY
          if (LEN(@text) = 16) SET @text = replace(@text, ' ', 'T') + ':00.000'
          else if (LEN(@text) = 19) SET @text = replace(@text, ' ', 'T') + '.000'
          else SET @text = replace(@text, ' ', 'T') 
          PRINT 'trydate:' + @text
          SELECT @res =convert(datetime, @text, 126)
      END TRY
      BEGIN CATCH
           print XACT_STATE() 
           print 'Catch'
          PRINT ERROR_SEVERITY()
          PRINT 'errordate:' + @text
      END CATCH
      print XACT_STATE() 
      print 'End'
  END