problem using ISDATE in a Function

Question

Post reply

problem using ISDATE in a Function

RichardBo

Ten Centuries

Points: 1262
More actions
September 21, 2011 at 8:05 am

#244205

I have a function that uses ISDATE to determine whether a varchar passed in is valid.
I know that the value of DATEFORMAT will alter the behaviour of ISDATE so how can I validate a date passed (knowing it's expected to be in DD/MM/YYYY HH:MM:SS).
I really don't want to add my own date validation.
I could take out the ISDATE, but if the date is invalid the function would error. Could I trap that error within the function nd then return a NULL?
The actual function is:
CREATE FUNCTION [fnDDMMYYYYHHMMSStoDate] (
@inDDMMYYYYHHMMSS varchar(200)
)
RETURNS DateTime
AS
BEGIN
DECLARE @outDate DateTime
DECLARE @TextDate varchar(200)
SELECT @TextDate = SUBSTRING(@inDDMMYYYYHHMMSS,7,4) + '-'
+ SUBSTRING(@inDDMMYYYYHHMMSS,4,2) + '-'
+ SUBSTRING(@inDDMMYYYYHHMMSS,1,2) + ' '
+ SUBSTRING(@inDDMMYYYYHHMMSS,12,8)
-- Format of @TextDate is 'YYYY-MM-DD HH:MM:SS'
IF LEN(@TextDate) <> 19 OR ISDATE(@TextDate) = 0
BEGIN
RETURN NULL
END
SELECT @outDate = CONVERT(datetime,@TextDate,120)
RETURN @outDate
END
An example of the DATEFORMAT behaviour is:
set dateformat mdy
select ISDATE('2011-08-31 14:34:56')
Returns 1
set dateformat ymd
select ISDATE('2011-08-31 14:34:56')
Returns 1
set dateformat dmy
select ISDATE('2011-08-31 14:34:56')
Returns 0
I can't see an obvious answer to this one!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The only reference I have is BOL.

Why the function return the data the way it does is beyond me.

Regarding the function [fnDDMMYYYYHHMMSStoDate]:

Wouldn't it be easier to simply use CONVERT (once the format is known)?

DECLARE @val CHAR(19)

SET @val='31/08/2011 14:34:56'

SELECT CONVERT(DATETIME,@val,103)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

RichardBo Ten Centuries Points: 1262 More actions · Answer 2

I accept that the function boils down to a CONVERT, but the problem is really about validating the string passed before calling CONVERT.

Eg

DECLARE @val CHAR(19)

SET @val='08/31/2011 14:34:56'

SELECT CONVERT(DATETIME,@val,103)

fails with

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Validating the date string with ISDATE will depend on the value of DATEFORMAT, but I can't change it's vaue in the function.

Is there a way of handling the error from the CONVERT from within the function?

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

RichardBo (9/22/2011)
I accept that the function boils down to a CONVERT, but the problem is really about validating the string passed before calling CONVERT.
Eg
DECLARE @val CHAR(19)
SET @val='08/31/2011 14:34:56'
SELECT CONVERT(DATETIME,@val,103)
fails with
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Validating the date string with ISDATE will depend on the value of DATEFORMAT, but I can't change it's vaue in the function.
Is there a way of handling the error from the CONVERT from within the function?

I have a CLR that allows me to do RegEx in my database.

USE [Test]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReplaceMatch]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[ReplaceMatch]

GO

IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'RegEx' and is_user_defined = 1)

DROP ASSEMBLY [RegEx]

GO

USE [Test]

GO

CREATE ASSEMBLY [RegEx]

AUTHORIZATION [dbo]

FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300604E7B4E0000000000000000E00002210B010800000A000000060000000000002E280000002000000040000000004000002000000002000004000000000000000400000000000000008000000002000000000000030040850000100000100000000010000010000000000000100000000000000000000000E02700004B000000004000006003000000000000000000000000000000000000006000000C000000542700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000003408000000200000000A000000020000000000000000000000000000200000602E72737263000000600300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000010280000000000004800000002000500FC2000005806000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B300300870000000100001100000F00280F00000A2D120F01280F00000A2D090F02280F00000A2B01170B072D40000F00281000000A0F01281000000A281100000A16FE010B072D220F00281000000A0F01281000000A0F02281000000A281200000A281300000A0ADE25002B087E1400000A0ADE1A00DE0A26007E1400000A0ADE0D001202FE1502000001080A2B0000062A0001100000000001006C6D000A010000011E02281500000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C0000001C020000237E000088020000EC02000023537472696E6773000000007405000008000000235553007C0500001000000023475549440000008C050000CC00000023426C6F620000000000000002000001471502000900000000FA2533001600000100000012000000020000000200000003000000150000000C00000001000000010000000300000000000A0001000000000006002A0023000A0052003D000600AD009B000600C4009B000600E1009B00060000019B00060019019B00060032019B0006004D019B00060068019B000600A00181010600B4019B000600E001CD013700F40100000600230203020600430203020A007C0261020E00C502A6020000000001000000000001000100010010001400140005000100010050200000000096005C000A000100F420000000008618690015000400000001006F00000002007B00000003008800190069001900210069001900290069001900310069001900390069001900410069001900490069001900510069001900590069001E0061006900190069006900230079006900290081006900150089006900150011009102330011009C0237009100CB023B009100D30241001100DB0248001100E7024E00090069001500200073002E002E002B005A002E00130065002E001B0065002E0023006B002E000B005A002E0033007A002E003B0065002E004B0065002E005B009B002E006300A4002E006B00AD0052000480000001000000BA10F0690000000000001400000002000000000000000000000001001A0000000000020000000000000000000000010031000000000002000000000000000000000001002300000000000000003C4D6F64756C653E0052656745782E646C6C005265674578006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E67005265706C6163654D61746368002E63746F7200696E707574537472696E67006D617463685061747465726E007265706C6163656D656E745061747465726E0053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F49734E756C6C006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780049734D61746368005265706C616365006F705F496D706C69636974004E756C6C0000032000000000001E78D965909B314580DBFD0A7B0352280008B77A5C561934E0890A0003110911091109110903200001042001010E042001010205200101113904200101080401000000032000020320000E050002020E0E0600030E0E0E0E05000111090E0306110907070311090211090A010005526567457800000501000000000E0100094D6963726F736F667400002001001B436F7079726967687420C2A9204D6963726F736F6674203230313100000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000604E7B4E00000000020000006E0000007027000070090000525344531B7CA5D062A8E844BB3B65F9037A19C002000000633A5C75736572735C6372616967775C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C52656745785C52656745785C6F626A5C44656275675C52656745782E7064620000000828000000000000000000001E280000002000000000000000000000000000000000000000000000102800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000080300000000000000000000080334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000100F069BA1000000100F069BA103F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00468020000010053007400720069006E006700460069006C00650049006E0066006F00000044020000010030003000300030003000340062003000000034000A00010043006F006D00700061006E0079004E0061006D006500000000004D006900630072006F0073006F00660074000000340006000100460069006C0065004400650073006300720069007000740069006F006E000000000052006500670045007800000040000F000100460069006C006500560065007200730069006F006E000000000031002E0030002E0034003200380032002E00320037003100320030000000000034000A00010049006E007400650072006E0061006C004E0061006D0065000000520065006700450078002E0064006C006C0000005C001B0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D006900630072006F0073006F006600740020003200300031003100000000003C000A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000520065006700450078002E0064006C006C0000002C0006000100500072006F0064007500630074004E0061006D0065000000000052006500670045007800000044000F000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0034003200380032002E00320037003100320030000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0034003200380032002E00320037003100320030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000303800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION [dbo].[ReplaceMatch](@inputString [nvarchar](4000), @matchPattern [nvarchar](4000), @replacementPattern [nvarchar](4000))

RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [RegEx].[RegEx.RegEx].[ReplaceMatch]

GO

The code is nothing special: -

using Microsoft.SqlServer.Server;

using System.Data.SqlTypes;

using System.Text.RegularExpressions;

// namespace to work with regular expressions

namespace RegEx

{

public class RegEx

{

[SqlFunction]

public static SqlString ReplaceMatch(

SqlString inputString,

SqlString matchPattern,

SqlString replacementPattern)

{

try

{

// input parameters must not be NULL

if (!inputString.IsNull &&

!matchPattern.IsNull &&

!replacementPattern.IsNull)

{

// check for first pattern match

if (Regex.IsMatch(inputString.Value,

matchPattern.Value))

// match found, replace using second pattern and return result

return Regex.Replace(inputString.Value,

matchPattern.Value,

replacementPattern.Value);

}

else

// if any input paramater is NULL, return NULL

return SqlString.Null;

}

catch

{

// on any error, return NULL

return SqlString.Null;

}

return new SqlString();

}

};

}

This now allows you to run the following: -

DECLARE @val CHAR(19)

SET @val='31/08/2011 14:34:56'

SELECT CONVERT(DATETIME,(dbo.ReplaceMatch(@val,

'(?n:^(?=\d)((?<day>31(?!(.0?[2469]|11))|30(?!.0?2)|

29(?(.0?2)(?=.{3,4}(1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|

(16|[2468][048]|[3579][26])00))|0?[1-9]|1\d|2[0-8])(?<sep>[/.-])(?<month>0?[1-9]|1[012])\2(?<year>(1[6-9]|[2-9]\d)\d{2})(?:

(?=\x20\d)\x20|$))?(?<time>((0?[1-9]|1[012])(:[0-5]\d){0,2}(?i:\ [AP]M))|([01]\d|2[0-3])(:[0-5]\d){1,2})?$)',

@val)),103)

The regular expression validates the date, so in the above case it returns "NULL".

--EDIT--

Hmm, my SQL code block managed to have a smiley in it. . . corrected 🙂

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 4

Well, since you can't use a TRY-CATCH construct in a user-defined function and you can't use SET DATEFORMAT in a user-defined function, either, what about setting up the code that calls the function to use a TRY-CATCH construct to change the DATEFORMAT setting depending on whether the function executes properly or returns an error?

Here's the function I created to test this idea:

CREATE FUNCTION dbo.test_date_convert (@test_date char(19))

RETURNS DATETIME

AS

BEGIN

DECLARE @return_date datetime

SET @return_date = CAST(@test_date as datetime)

RETURN @return_date

END

I used CAST instead of CONVERT here since CASTing character strings to datetimes sometimes works with certain DATEFORMAT settings and string values when CONVERTing them would not. You can't specify a style with CAST, but many people would argue that applying a style to a datetime value should be done by the application layer anyway.

Here's the code I used to call the function in nested TRY-CATCH blocks that change the DATEFORMAT when the function call returns the error:

DECLARE @test_date char(19), @result_date datetime

SET @test_date = '31-08-2011 14:36:24'

BEGIN TRY

SET DATEFORMAT mdy

SET @result_date = dbo.test_date_convert(@test_date)

PRINT 'TRY SUCCEEDED'

END TRY

BEGIN CATCH

BEGIN TRY

SET DATEFORMAT dmy

SET @result_date = dbo.test_date_convert(@test_date)

PRINT 'CATCH 1 SUCCEEDED'

END TRY

BEGIN CATCH

SET DATEFORMAT ymd

SET @result_date = dbo.test_date_convert(@test_date)

PRINT 'CATCH 2 SUCCEEDED'

END CATCH

SELECT @result_date

I tried it with all different combinations of MM-DD-YYYY, DD-MM-YYYY, and YYYY-MM-DD string formats (using both '-' and '/' as a separator) and got the desired result each time. It does appear that the second TRY-CATCH is probably superfluous because the function can CAST YYYY-MM-DD strings to datetime when DATEFORMAT is mdy.

I don't know if you can implement this idea in your calling code, but if you have no way to validate the format of the date strings being passed to the database, I'm not sure what else you can do to handle datatype conversion errors.

Jason Wolfkill

RichardBo Ten Centuries Points: 1262 More actions · Answer 5

Thank you for your thoughts. It seems that there isn't an easy way to validate a date within a function (if ISDATE doesn't work for you). The CLR solution is neat as long as you don't have to revisit it and understand what to do with it.

What I decided to do is create a table with each valid date in it. Validating a date then just becomes SELECT Date FROM Dates WHERE DDMMMYYYY = '<Value>' it returns nothing if it's not valid and the date if it is.

It also gives me additional benefits of having a valid range for my dates.

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 6

RichardBo (9/27/2011)
Thank you for your thoughts. It seems that there isn't an easy way to validate a date within a function (if ISDATE doesn't work for you). The CLR solution is neat as long as you don't have to revisit it and understand what to do with it.
What I decided to do is create a table with each valid date in it. Validating a date then just becomes SELECT Date FROM Dates WHERE DDMMMYYYY = '<Value>' it returns nothing if it's not valid and the date if it is.
It also gives me additional benefits of having a valid range for my dates.

This seems like a good solution to me, especially if you have a limited range of acceptable dates. I re-read your original post, though, and noticed that you said the varchar() value "is expected" to be passed as DDMMYYYY (with some time info, too). Does the application that passes the varchar() value strictly control the format, and can you ensure that it always will? Otherwise, can you be sure that 01082011 is 01 Aug 2011 and not 08 Jan 2011? Needless to say, this kind of confusion and uncertainty would cause a world of hurt. This is one of the many reasons that experienced database developers recommend against accepting, storing, and returning dates as varchar() data types and, if that is unavoidable, to insist on an ISO 8601 compliant format (e.g., YYYYMMDD, YYYY-MM-DD) so that there is no ambiguity.

Best wishes,

JW

Jason Wolfkill

RichardBo Ten Centuries Points: 1262 More actions · Answer 7

While the solution worked (using a table of dates) it was very slow when used on large tables so has been scrapped.

I have found a method that does what I want -

I am expecting the date passed to be in the format DD/MM/YYYY and change it to YYYY-MM-DD before the CONVERT(DateTime,@d,120) which works whatever the DATEFORMAT.

I have found that ISDATE will return 1 for at least 1 of the two formats (DD/MM/YYYY and YYYY-MM-DD) if the date is valid for any value of DATEFORMAT.

I call ISDATE for both date formats. If one returns 1, the date is valid.

This little test shows the problem:

set dateformat DMY