This article is based on the a question from Jr. Developer at my company. He works with the system, where we import CSV file in our database. One of the fields in the database is DATETIME field. Due to architecture requirement, we insert all the CSV fields in the temp table which has all the fields VARCHAR. We validate all the data first in temp table (check for inconsistency, malicious code, incorrect data type) and if passed validation we insert them in the final table in the database. Let us learn about ISDate function in this blog post.
We always checked DATETIME field for incorrect data type. One of the user input dates as 30/2/2007. The date was successfully inserted in the temp table but while inserting from temp table to final table it crashed with error. We had now task to validate incorrect date value before we insert in the final table. A junior Developer asked me how can he do that? We check for incorrect data type (varchar, int, NULL) but this is incorrect date value. Regular expression works fine with them because of mm/dd/yyyy format.
After a long history of the problem the solution is very simple. We now use ISDATE() function to validate if the date is valid.
Examples:
----Invalid date
SELECT ISDATE('30/2/2007')RETURNS : 0 (Zero)
----Valid date
SELECT ISDATE('12/12/2007')RETURNS : 1 (ONE)
----Invalid DataType
SELECT ISDATE('SQL')RETURNS : 0 (Zero)
Reference : Pinal Dave (https://blog.sqlauthority.com)
41 Comments. Leave new
select isdate(’02-015-2013′) returns 1
select convert(date, ’02-015-2013′) throws and error.
Solved
select convert(varchar(10),convert(date, convert(datetime(’02-015-2013′)), 101)
I wanted a character string as part of a case statement
Hi ,
I have an requirement like this.Before inserting the data from one table to another table certain validations has to be passed.Kindly Suggest me solution for this.
Hi in below table structure i m trying to validate start and enddate using isvalidate function but it returning ‘0’ please help.
CREATE TABLE [dbo].[ST_TargetUpload](
[UserID] [bigint] NULL,
[REGION] [varchar](50) NULL,
[COMPCODE] [varchar](50) NULL,
[Cust_Code] [varchar](50) NULL,
[Cust_Id] [varchar](50) NULL,
[StartDate] [nvarchar](50) NULL,
[EndDate] [nvarchar](50) NULL,
[QuarterID] [varchar](50) NULL,
[Year] [varchar](50) NULL,
[TargetType] [varchar](50) NULL,
[Target] [varchar](50) NULL,
[Flag] [char](10) NULL,
[ErrorMsg] [varchar](150) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[ST_TargetUpload] ([UserID], [REGION], [COMPCODE], [Cust_Code], [Cust_Id], [StartDate], [EndDate], [QuarterID], [Year], [TargetType], [Target], [Flag], [ErrorMsg]) VALUES (NULL, N’EAST’, N’13305639′, N’CKB002301′, N’2080012301′, N’01/01/2016
‘, N’03/31/2016
‘, N’1′, N’2016′, N’Volume’, N’50’, N’E ‘, N’Start Date and End Date should be in MM/DD/YYYY Format’)
GO
INSERT [dbo].[ST_TargetUpload] ([UserID], [REGION], [COMPCODE], [Cust_Code], [Cust_Id], [StartDate], [EndDate], [QuarterID], [Year], [TargetType], [Target], [Flag], [ErrorMsg]) VALUES (NULL, NULL, NULL, N’CKB002301′, NULL, NULL, NULL, NULL, NULL, NULL, NULL, N’E ‘, N’Start Date and End Date should be in MM/DD/YYYY Format’)
GO
INSERT [dbo].[ST_TargetUpload] ([UserID], [REGION], [COMPCODE], [Cust_Code], [Cust_Id], [StartDate], [EndDate], [QuarterID], [Year], [TargetType], [Target], [Flag], [ErrorMsg]) VALUES (NULL, NULL, NULL, NULL, N’2080012301′, NULL, NULL, NULL, NULL, NULL, NULL, N’E ‘, N’Start Date and End Date should be in MM/DD/YYYY Format’)
GO
Thank you.
Hi,
DECLARE @V DATE = ‘0001-01-01’
SELECT ISDATE(@V)
Getting the following wrror
Msg 8116, Level 16, State 1, Line 8
Argument data type date is invalid for argument 1 of isdate function.
But SELECT ISDATE(‘0001-01-01’) is working properly
ISDATE works on datetime datatype only whose date range is from 1753-01-01 through 9999-12-31. You declared DATE variable which is not working. When used literal value directly, it is implicitly converted to datetime
SELECT ISDATE( ‘3367’ ) — RESULT 1 WHY?