March 16, 2015 at 11:00 am
Why does this code (below) cause a change in the transaction state?
DECLARE @date DATE,
@string VARCHAR(5)= 'test'
BEGIN TRAN
PRINT XACT_STATE()
BEGIN TRY
-- see if the data I've been given can be cast to something else
SET @date = CAST(@String AS DATE)
END TRY
BEGIN CATCH
PRINT XACT_STATE()
END CATCH
ROLLBACK TRAN
March 16, 2015 at 12:13 pm
Varchar (5) that's why.
The below does not throw an error.
dECLARE @date DATE,
@string VARCHAR(10)= '2015-01-01'
BEGIN TRAN
PRINT XACT_STATE()
BEGIN TRY
-- see if the data I've been given can be cast to something else
SET @date = CAST(@String AS DATE)
END TRY
BEGIN CATCH
PRINT XACT_STATE()
END CATCH
ROLLBACK TRAN
March 17, 2015 at 3:21 am
I don't think you quite understand the issue. I'm attempting to parse a non-date into a date variable. Yes this sounds stupid, but I'm testing whether or not it can. If soemthing can't parse (in my original code) I substitute the value for something else.
Basically I'm trying to recreate the functionality of TryParse but in 2008 R2.
And so that's what the example is about.
March 17, 2015 at 3:57 am
Quick suggestion, use TRY_CONVERT
😎
DECLARE @DATEVAR DATE = NULL;
DECLARE @STRVAR VARCHAR(50) = 'Not A Date';
BEGIN TRAN
PRINT XACT_STATE()
BEGIN TRY
SELECT @DATEVAR = TRY_CONVERT(DATE,@STRVAR)
END TRY
BEGIN CATCH
PRINT XACT_STATE()
END CATCH
ROLLBACK TRAN
March 17, 2015 at 4:23 am
Unfortunately, as much as I'd love to use TRY_CONVERT(), we are stuck in the dark ages using 2k8R2
March 17, 2015 at 4:33 am
March 17, 2015 at 7:49 am
MadAdmin (3/17/2015)
try using IsDate() and then return error codes if not.
That can be flawed as it depends on your date format, eg doing
SELECT ISDATE('31-01-2015')
SELECT ISDATE('31/01/2015')
On my machine both return False, even though they are valid UK date formats, however both these return TRUE
SELECT ISDATE('01-31-2015')
SELECT ISDATE('01/31/2015')
At best there is a possible issue in using the ISDATE function, you also have to consider when converting is '01-02-2015' the 1st of Feb, or the 2nd Jan?
However this code works on SQL 2012 and SQL 2008R2
DECLARE @Date DATE
DECLARE @StrVar VARCHAR(5) = 'Not a date'
BEGIN TRAN
BEGIN TRY
SELECT @Date = CAST(@StrVar as DATE)
END TRY
BEGIN CATCH
Print Error_Message ()
Print XACT_STATE()
END CATCH
ROLLBACK TRAN
You have to think that that conversion could be used to update a table or inserting data into a new table, so on an error you would want to roll back the whole transaction if it failed, wouldn't you?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 17, 2015 at 8:07 am
If you want to mimic the TRY_PARSE or TRY_CONVERT functions that are in 2012, then you may have to resort to a CLR to accomplish this, as you cant use TRY...CATCH in a function, and ISDATE can create false positives (earlier post!!).
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 17, 2015 at 8:08 am
chris.rogers00 (3/16/2015)
Why does this code (below) cause a change in the transaction state?
DECLARE @date DATE,
@string VARCHAR(5)= 'test'
BEGIN TRAN
PRINT XACT_STATE()
BEGIN TRY
-- see if the data I've been given can be cast to something else
SET @date = CAST(@String AS DATE)
END TRY
BEGIN CATCH
PRINT XACT_STATE()
END CATCH
ROLLBACK TRAN
From BOL: "The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction."
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply