validate a date in t-sql?

Question

I just want to validate a given input from the user

Declare @UserInput NVARCHAR(20)
set @UserInput = '26/07/2013'
select ISDATE(@UserInput)

This will return false as the date is in australian format, even though the date is valid

I can change the last line to the folowing

select isdate(CONVERT(datetime, @UserInput, 103))

and it works. But if the @Userinput was rubbish (ie:- 'hello'), then the last statement would fail. How can I have something, where no matter what the user enters, it validates it to an australian date (dd/mm/yyyy)?

Thanks

Answer 1

Use SET DATEFORMAT to specify the format you are expecting the date to be entered in:

SET DATEFORMAT DMY;

Declare @UserInput NVARCHAR(20)
set @UserInput = '26/07/2013'
select ISDATE(@UserInput)

I would be inclined to perform such validations prior to the input reaching SQL-Server, and ensuring that any date variables are dates.