locked
How to detect if a string can be converted to a datetimeoffset? RRS feed

  • Question

  • If you use CONVERT(datetimeoffset,@string) and the string is not a valid datetimeoffset, this results in an error. I want to make a call in a transaction and ignore that field if the convert fails. A try catch around the convert doesn't work as it forces a full rollback of the transaction (the transaction becomes uncommitable for some reason).

    Ordinarily with a datetime type, you would first call ISDATE(@string) to see but ISDATE does not work on DATETIMEOFFSETs. I don't want to reinvent the wheel. Is there some trick to doing this?

    Thanks in advance,
    SC
    Thursday, June 4, 2009 6:27 AM

Answers

  • This is what BOL (Jan 2009) says:

    ISDATE returns 0 for user-defined variables or database columns of time,
    date, datetime2, or datetimeoffset types. ISDATE returns 0 for character
    strings that have more than three positions of scale for fractional
    seconds precision or with the time zone offset date part. To validate
    values of these data types, use the CONVERT function with the
    corresponding data type argument and handle error 241. Error 241 returns
    the message: "Conversion failed when converting date and/or time from
    character string."

    Interesting that info has been removed in the latest on-line BOL version
    (May 2009):
    http://msdn.microsoft.com/en-us/library/ms187347.aspx?ppud=4

    You may be able to write your own ISDATE function using CLR to validate
    the new data types.

    Here is a related Connect item to vote for:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354766

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    • Marked as answer by SamCPP Thursday, June 4, 2009 12:55 PM
    Thursday, June 4, 2009 12:34 PM

All replies

  • Looks like they missed a trick on ISDATE as it no longer works for valid values for the new DATE and DATETIME2 fields either.

    I think MS need to look at this a function ISDATE2 for the three new types would be good.

    apart from that you could write a function which could strip the +hh: part use ISDATE on the front and validate the back manually.

    Thursday, June 4, 2009 12:13 PM
  • This is what BOL (Jan 2009) says:

    ISDATE returns 0 for user-defined variables or database columns of time,
    date, datetime2, or datetimeoffset types. ISDATE returns 0 for character
    strings that have more than three positions of scale for fractional
    seconds precision or with the time zone offset date part. To validate
    values of these data types, use the CONVERT function with the
    corresponding data type argument and handle error 241. Error 241 returns
    the message: "Conversion failed when converting date and/or time from
    character string."

    Interesting that info has been removed in the latest on-line BOL version
    (May 2009):
    http://msdn.microsoft.com/en-us/library/ms187347.aspx?ppud=4

    You may be able to write your own ISDATE function using CLR to validate
    the new data types.

    Here is a related Connect item to vote for:
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=354766

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    • Marked as answer by SamCPP Thursday, June 4, 2009 12:55 PM
    Thursday, June 4, 2009 12:34 PM
  • Yes Plamen that is the exact issue I am having a lot of trouble with. I am writing my own function to do the check now.

    P.s. added a vote on connect.

    Thanks all... good and bad to see I'm not the only one wondering about this.

    Cheers,
    S
    Thursday, June 4, 2009 12:55 PM
  • I know its  a rather simplified sample, but you can use a case statement or control flow logic to determine if the column can be fully converted using ISDATE(). Essentially the string is either a date or it is not.  If the string is a valid date then you can convet it to a datetimeoffset; otherwise, you can  do something else.  I know this is more of a workaround, but it should get the job done for what you need.

    DECLARE @Dates TABLE(
    ID INT IDENTITY(1,1),
    dt VARCHAR(20)
    )
    
    INSERT INTO @Dates ([dt])
    VALUES ('2009-06-30');
    
    INSERT INTO @Dates ([dt])
    VALUES (GETDATE());
    
    INSERT INTO @Dates ([dt])
    VALUES ('2009-01-01 10:00 PM');
    
    SELECT
    	CASE
    	WHEN ISDATE(dt) = 1
    	THEN CONVERT(DATETIMEOFFSET,dt)
    	ELSE NULL
    	END
    FROM @Dates
    
    IF NOT EXISTS(
    	SELECT 1
    	FROM @Dates
    	WHERE
    		ISDATE(dt) = 0
    )
    BEGIN
    	SELECT
    		ID,
    		CONVERT(DATETIMEOFFSET,dt)
    	FROM @Dates
    END
    	

    http://jahaines.blogspot.com/
    Thursday, June 4, 2009 1:56 PM
  • Adam,

    The problem is that ISDATE returns 0 for valid DATETIMEOFFSET values.

    Try this:

    DECLARE @Dates TABLE(
    ID INT IDENTITY(1,1),
    dt VARCHAR(40)
    );

    INSERT INTO @Dates ([dt])
    VALUES ('2009-01-01 22:00:00.0000000 +05:00');

    SELECT
    CASE
    WHEN ISDATE(dt) = 1
    THEN CONVERT(DATETIMEOFFSET,dt)
    ELSE NULL
    END
    FROM @Dates;

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Thursday, June 4, 2009 2:19 PM
  • Looking back at this one, here is a Connect suggested workaround:

    select CASE WHEN TRY_CONVERT(datetimeoffset, '2008-07-02 22:49:06.3593750 +05:30') IS NULL THEN 0 ELSE 1 END as is_datetimeoffset, CASE WHEN TRY_CONVERT(datetime2, '2008-07-02 22:49:06.3593750') IS NULL THEN 0 ELSE 1 END as is_datetime2

    Wednesday, March 27, 2013 6:22 AM