在 t-sql 中验证日期?

问题描述

我只想验证来自用户的给定输入

I just want to validate a given input from the user

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

这将返回 false，因为日期是澳大利亚格式，即使日期有效

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))

它的工作原理.但是如果@Userinput 是垃圾(即:- 'hello')，那么最后一条语句就会失败.我怎样才能拥有一些东西，无论用户输入什么，它都会将其验证为澳大利亚日期 (dd/mm/yyyy)?

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)?

谢谢

推荐答案

使用 SET DATEFORMAT 指定您希望输入的日期格式:

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)

我倾向于在输入到达 SQL-Server 之前执行此类验证，并确保任何日期变量都是日期.

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

这篇关于在 t-sql 中验证日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！