I usually don't expect the ISDATE function to return predictable results because it is dependent on the specific date datatype you are converting to and often gives different results (0/1) with the same date-like looking string passed in if the types are different. So I use specific date validation procedures I've cobbled together. The TRY CATCH method is best but those don't work in functions so unfortunately a procedure must be built to implement the TRY CATCH validation method. Right now what I have to offer is RBAR and I apologize for that. If someone can convert my solution to still use TRY CATCH and get rid of the cursor loop I'll be grateful for the contribution.
First thing for testing is to build some random dates. I'm including a date format function just so I can input dates in various forms and then build a sample table.
CREATE FUNCTION [dbo].[tvfFormatDateWithMask]
(
@date AS DATETIME
,@format_string VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
WITH fourRows(N)
AS (
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
)
,cteTally(N)
AS (
SELECT TOP (50)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
fourRows AS A
CROSS JOIN fourRows AS B
CROSS JOIN fourRows AS C
ORDER BY 1
)
,tokenizedString
AS (
SELECT
N
,C
,groupId = DENSE_RANK() OVER (ORDER BY C, _groupId)
FROM
(
SELECT
N
,SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) AS C
,_groupId = N - ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) ORDER BY N)
FROM
cteTally
WHERE
N <= LEN(@format_string)
) AS fs)
SELECT
formattedDate =
(SELECT
CASE REPLICATE(MIN(C),COUNT(*))
WHEN 'YYYY' THEN RIGHT('0000' + CAST(YEAR(@date) AS NVARCHAR(4)),4)
WHEN 'YY' THEN RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),2)
WHEN 'Y' THEN CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),1) AS INT) AS NVARCHAR(2))
WHEN 'MMMM' THEN DATENAME(month,@date)
WHEN 'MM' THEN RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR(2)),2)
WHEN 'M' THEN CAST(MONTH(@date) AS NVARCHAR(2))
WHEN 'DDDD' THEN DATENAME(weekday,@date)
WHEN 'DD' THEN RIGHT('00' + CAST(DAY(@date) AS NVARCHAR(2)),2)
WHEN 'D' THEN CAST(DAY(@date) AS NVARCHAR(2))
WHEN 'HH' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS NVARCHAR(2)),2)
WHEN 'H' THEN CAST(DATEPART(hour,@date) AS NVARCHAR(2))
WHEN 'hh' THEN RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
WHEN 'h' THEN
CASE WHEN LEFT(
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2),1) = 0 THEN
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),1)
ELSE
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
END
WHEN 'mm' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS NVARCHAR(2)),2)
WHEN 'm' THEN CAST(DATEPART(minute,@date) AS NVARCHAR(2))
WHEN 'ss' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS NVARCHAR(2)),2)
WHEN 's' THEN CAST(DATEPART(second,@date) AS NVARCHAR(2))
WHEN 'fff' THEN RIGHT('000' + CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)),3)
WHEN 'f' THEN CAST(DATEPART(millisecond,@date) AS NVARCHAR(3))
WHEN 'tt' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'PM'
ELSE N'AM'
END
WHEN 't' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'P'
ELSE N'A'
END
WHEN 'x' THEN
CASE
WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st'
WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd'
ELSE N'th'
END
ELSE MIN(C)
END
FROM
tokenizedString
GROUP BY
groupId
ORDER BY
MIN(N)
FOR
XML PATH('')
,TYPE
).value('(./text())[1]','nvarchar(50)')
)
/*
SELECT FormattedDate FROM dbo.tvfFormatDateWithMask(GETDATE(),'DD/MM/YY') AS CurrDate
or
SELECT
FormattedDate
FROM
(
SELECT
GETDATE() AS UnformattedDate
) Result
CROSS APPLY
dbo.tvfFormatDateWithMask(Result.UnformattedDate,'DD/MM/YY')
'YYYY'- full year with century
'YY'- year without century
'Y'- last digit of year
'MMMM'- month name
'MM'- month number with leading zero
'M'- month number without leading zero)
'DDDD'- day name
'DD'- day number with leading zero
'D'- day number without leading zero
'HH'- hour with leading zero (24 hr format)
'H'- hour without leading zero
'hh'- hour with leading zero (12 hr format)
'h'- hour without leading zero (12 hr format)
'mm'- minutes with leading zero
'm'- minutes without leading zero
'ss'- seconds with leading zero
's'- seconds without leading zero
'fff'- milliseconds with leading zeros
'f'- milliseconds without leading zeros
'tt'- AM or PM
't'- A or P
'x'- st, nd, or th
*/
Now create the date validation procedure itself:
CREATE PROCEDURE [dbo].[IsValidDate]
@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYM
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT @sDateFormat
DECLARE
@dStdDate DATETIME2 --use the date datatype you want to check against
,@bIsValidDate BIT
,@ERROR BIT
SET @dStdDate = NULL
BEGIN TRY
SET @dStdDate = CONVERT(DATETIME2,@sDate)
SET @bIsValidDate = 1
END TRY
BEGIN CATCH
SET @bIsValidDate = 0
SET @dStdDate = NULL
END CATCH
SET @ERROR = @bIsValidDate
SELECT
@sDate AS InputDate
,@dStdDate AS StdDate
,@ERROR AS IsValidDate
/*
EXAMPLES:
EXEC dbo.IsValidDate '01-07-2001' -- Valid date
EXEC dbo.IsValidDate '1/7/2001' -- Valid date
EXEC dbo.IsValidDate '07-01-2001' -- Valid date
EXEC dbo.IsValidDate '7/1/2001' -- Valid date
EXEC dbo.IsValidDate '29-12-2013','DMY' -- Valid date in DMY format
EXEC dbo.IsValidDate '29-12-2013','MDY' -- Invalid date in MDY format
EXEC dbo.IsValidDate '32-12-2013' -- ERROR: Date out of range
EXEC dbo.IsValidDate '29-02-2013' -- ERROR: Not a leap year
EXEC dbo.IsValidDate 'xyz' -- ERROR: Invalid date
EXEC dbo.IsValidDate '1234' -- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208' -- Valid date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606' -- Invalid std date/valid umc date
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30' -- Invalid std date/valid umc date
*/
END
Now for the script to create 10,000 rows of sample dates with some bad dates sprinkled in.
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[SomeDate] VARCHAR(50) NULL,
PRIMARY KEY (ID))
IF OBJECT_ID('tempdb..#DateValidationResults') IS NOT NULL
DROP TABLE #DateValidationResults
CREATE TABLE #DateValidationResults (
[ID] INT IDENTITY(1,1) NOT NULL,
[InputDate] VARCHAR(50) NULL,
[ConvertedDate] VARCHAR(50) NULL,
[IsValidDate] BIT NULL,
PRIMARY KEY (ID))
SET NOCOUNT ON
BEGIN
INSERT INTO #TempTable
SELECT TOP 10000
(CASE
WHEN t1.N % 50 = 0
THEN 'ABC-DEFG-9999-XYZ' -- BAD DATE
WHEN t1.N % 5 = 0
THEN (SELECT formattedDate FROM dbo.tvfFormatDateWithMask(CAST(RAND(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))*3653.0+(SELECT MyRand*100000 FROM dbo.iFunction) AS DATETIME),'DD/MM/YYYY hh:mm'))
WHEN t1.N % 3 = 0
THEN (SELECT formattedDate FROM dbo.tvfFormatDateWithMask(CAST(RAND(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))*3653.0+(SELECT MyRand*100000 FROM dbo.iFunction) AS DATETIME),'YY/MM/DD'))
WHEN t1.N % 2 = 0
THEN (SELECT formattedDate FROM dbo.tvfFormatDateWithMask(CAST(RAND(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))*3653.0+(SELECT MyRand*100000 FROM dbo.iFunction) AS DATETIME),'YYYY-MM-DD hh:mm:ss.fff +hh:mm'))
ELSE
(SELECT formattedDate FROM dbo.tvfFormatDateWithMask(CAST(RAND(CHECKSUM((SELECT MyNewID FROM dbo.iFunction)))*3653.0+(SELECT MyRand*100000 FROM dbo.iFunction) AS DATETIME),'YYYY-MM-DD hh:mm:ss'))
END) AS SomeDate
FROM dbo.Tally t1
Finally, run the validation script:
DECLARE
@ID INT
,@chkDate VARCHAR(50)
DECLARE DateValidation CURSOR
FOR
SELECT
ID
,SomeDate
FROM
#TempTable
OPEN DateValidation
FETCH NEXT FROM DateValidation INTO
@ID, @chkDate
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
INSERT INTO #DateValidationResults
(InputDate, ConvertedDate, IsValidDate)
EXEC dbo.IsValidDate @chkDate,'YMD'
END TRY
BEGIN CATCH
INSERT INTO #DateValidationResults
(InputDate, ConvertedDate, IsValidDate)
VALUES (@ID,@chkDate,0)
END CATCH
SET @chkDate = NULL
FETCH NEXT FROM DateValidation INTO
@ID, @chkDate
END
CLOSE DateValidation
DEALLOCATE DateValidation
SELECT
*
FROM
#DateValidationResults