CODEWITHSUNDEEP
sql-serverdatedatetime
Evribadi
Evribadi

Reputation: 15

How to check if a input date is in a specific format - SQL SERVER

I'd like checking if an input date is in a specific format in Sql Server. I can't use function ISDATE because dateformat MDY doesn't support some format like 103 (dd/mm/yyyy), 104 (dd.mm.yyyy), 105 (dd-mm-yyyy).

If I try to convert, for example, '16/09/2015' like this convert(datetime,'16/09/2015',103) I get the cast, but obviously if I try to convert somethings like '16/0c/2015' I get an Exception.

So I'd like insert in WHERE condition a check that could prevent a cast of a not valid date.

I'd like create a specific function could execute this job. I tried to insert into my function case when condition like this:

 case 
        when @format in (103,104,105) then set language british 
 end
 isdate(@string)

or like this

 declare @result varchar(30)
 begin try
        SELECT @result = CONVERT(datetime,@string,@format)
        SET @ret = 1
        RETURN
    end try
    begin catch
        SET @ret = 0
        RETURN
    end catch;

but inside a function i can't insert try-catch or set language statement.

Can you help me?

Thanks

Fabio

Upvotes: 1

Views: 4468

Answers (1)

AdamL
AdamL

Reputation: 13161

You didn't say what version of Sql Server you're using. Starting from 2012, you can use TRY_CONVERT function, which accepts same datetime styles as CONVERT. You can then try to convert the input to many formats without getting any errors - if convert fails, you just get NULL.

In editions prior to 2012, you could go with creating custom CLR functions and doing it in .NET. It requires some work, but it's still better than going through hell of validating dates with mssql built in functions, because not only you'll have to determine the format, but also validate a date (30 or 31 days in month, leap years and so on). In .NET it's almost a one-liner.

Upvotes: 1

Related Questions