July 22, 2013 at 3:52 am
How To Validate a year, month,and date and totaldate?Year should be in yyyy format,month should be between
1 and 1 to 12 and date should be between 1 to 31 and total date should be YYYY-MM-DD Formate?
Plase guys share your ideas?
July 22, 2013 at 4:08 am
Can you provide more information about the context please Ananth? For instance, are you designing a validation process for an import of a text-typed date?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2013 at 4:20 am
If you want to validate text-strings in a table field, you can use a TALLY table to match the values against.
Create a table with a datetime field and fill it with all dates within the required range. Join this tally table with the table that holds the text-string values. Convert the datetime value of the tallytable to seperate values for year, month and day (P.S.: these values could also be added as extra columns in the tally-table). Match these year, month and day values to substrings of the text value.
create table tally_date (id int identity (1,1)
, date_value date)
declare @int int
set @int = 0
while @int < 366
begin
insert into tally_date
select dateadd(day, @int, '20120101')
set @int = @int + 1
end
select * from tally_date
create table validate_dates (date_value nvarchar(10))
insert into validate_dates
select '2012-02-00'
union allselect '2012-02-10'
union allselect '2012-02-29'
union allselect '2012-03-10'
union allselect '2012-03-21'
union allselect '2012-21-04'
-- join the textstring values with the tally-table and list all mismatches (i.e. invalid dates) first
select
vd.date_value
, left(vd.date_value, 4) as year_value
, substring(vd.date_value, 6, 2) as month_value
, right(vd.date_value, 2) as day_value
, td.date_value
from validate_dates vd
left outer join tally_date td
on left(vd.date_value, 4) = year(td.date_value)
and substring(vd.date_value, 6, 2) = month(td.date_value)
and right(vd.date_value, 2) = day(td.date_value)
order by
td.date_value
July 22, 2013 at 4:39 am
You have a date stored as varchar or similar and you want to check it's a valid date?
Two options - the first being vastly more preferable:
(1) Don't store dates as varchar. Use one of the date data types.
(2) Use CONVERT to convert it to date. If it fails, you don't have a valid date. Use the TRY...CATCH syntax to trap errors.
John
July 22, 2013 at 4:45 am
John Mitchell-245523 (7/22/2013)
You have a date stored as varchar or similar and you want to check it's a valid date?Two options - the first being vastly more preferable:
(1) Don't store dates as varchar. Use one of the date data types.
(2) Use CONVERT to convert it to date. If it fails, you don't have a valid date. Use the TRY...CATCH syntax to trap errors.
John
ISDATE() is useful for this too.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 22, 2013 at 5:52 am
Ananth@Sql (7/22/2013)
How To Validate a year, month,and date and totaldate?Year should be in yyyy format,month should be between1 and 1 to 12 and date should be between 1 to 31 and total date should be YYYY-MM-DD Formate?
Plase guys share your ideas?
What is an input for proposed validation?
If it's one single character value, your best way would be just using ISDATE function.
And about format. So, you want to be sure that if the date string provided, it is only in ISO format?
(Please not that YYYYMMDD is also standard ISO format).
I guess you can do some thing like that:
DECLARE @validIsoDate BIT = 0
IF ISDATE(@Value) = 1
BEGIN
IF CAST(YEAR(@Value) AS CHAR(4)) = LEFT(@Value,4) SET @validIsoDate =1
END
SELECT @validIsoDate
The best option would be the above written as ITV function...
July 22, 2013 at 5:56 am
Here is one of the short form which can be used for ITV function
RETURN SELECT ISNULL(CASE WHEN ISDATE(@value) = 1 THEN CASE WHEN CAST(YEAR(@Value) AS CHAR(4)) = LEFT(@Value,4) THEN 1 END END,0) AS ValidIsoDate;
July 22, 2013 at 8:42 am
The CASE expression will verify that the data string is in YYYY-MM-DD format, and is a valid date. It will return a 1 if the date is valid and a 0 if it isn't.
select
a.DT,
IsDateValid =
case
when a.DT is null
then 0
when a.DT not like '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'
then 0
when isdate(replace(a.DT,'-','')) <> 1
then 0
else 1
end
from
(
-- Test Date
select DT = '1752-12-31'union all
select DT = '1753-01-01'union all
select DT = '1900-02-28'union all
select DT = '1900-02-29'union all
select DT = '2000-02-29'union all
select DT = '2004 01-01'union all
select DT = '2004-01 01'union all
select DT = '2013-02-29'union all
select DT = '2013-06-30'union all
select DT = '2013-06-31'--union all
) a
order by
a.DT
Results:
DT IsDateValid
---------- -----------
1752-12-31 0
1753-01-01 1
1900-02-28 1
1900-02-29 0
2000-02-29 1
2004 01-01 0
2004-01 01 0
2013-02-29 0
2013-06-30 1
2013-06-31 0
July 22, 2013 at 9:41 am
ISDATE may give different results depending on the datetime datatype (DATE vs DATETIME vs DATETIME2, etc.). The best and surest method to validate a date-like string as a date is to attempt to convert it to the desired datetime datatype. If it's out of scope or an invalid date the conversion will fail. In this case I'm testing by attempting a conversion to SMALLDATETIME. That value should be changed accordingly as necessary.
Here's a procedure that will do the date validation (it will also handle UMC dates if you include the second nested procedure).
CREATE PROCEDURE [dbo].[IsValidDate]
@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'YMD' -- MDY, DMY, YMD, YDM, MYD, DYM
AS
BEGIN
/*
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
*/
SET NOCOUNT ON
SET DATEFORMAT @sDateFormat
DECLARE
@dStdDate SMALLDATETIME
,@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@bIsValidDate BIT
SET @dStdDate = NULL
SET @dUMCDate = NULL
/* Check to see if this is a valid UMC date */
IF OBJECT_ID('tempdb..#CheckUMCDate') IS NOT NULL
DROP TABLE #CheckUMCDate
CREATE TABLE #CheckUMCDate (
[ID] INT IDENTITY(1,1) NOT NULL,
[InputDate] VARCHAR(50) NULL,
[ConvertedDate] VARCHAR(50) NULL,
[IsValidUMCDate] BIT NULL
PRIMARY KEY (ID))
BEGIN TRY
INSERT INTO #CheckUMCDate
EXEC dbo.IsValidUMCDate @sDate
END TRY
BEGIN CATCH
INSERT INTO #CheckUMCDate
SELECT
@sDate AS InputDate
,NULL ConvertedDate
,0 AS IsValidUMCDate
END CATCH
SELECT
@dUMCDate = ConvertedDate
,@bIsValidUMCDate = IsValidUMCDate
FROM
#CheckUMCDate
/* Check date by converting into other date datatypes. */
/* The date datatypes to use can (should) be changed */
/* depending on your requirements. Different date */
/* datatypes will give different results! */
BEGIN TRY
SET @dStdDate = CONVERT(SMALLDATETIME,@sDate)
SET @bIsValidDate = 1
END TRY
BEGIN CATCH
SET @bIsValidDate = 0
SET @dStdDate = NULL
END CATCH
SELECT
@sDate AS InputDate
,@dStdDate AS StdDate
,@bIsValidDate AS IsValidDate
,@dUMCDate AS UMCDate
,@bIsValidUMCDate AS IsValidUMCDate
END
GO
CREATE PROCEDURE [dbo].[IsValidUMCDate]
@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'YMD' -- MDY, DMY, YMD, YDM, MYD, DYM
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT @sDateFormat
DECLARE
@dUMCDate DATETIMEOFFSET
,@bIsValidUMCDate BIT
,@ERROR BIT
SET @dUMCDate = NULL
BEGIN TRY
SET @dUMCDate = CONVERT(DATETIMEOFFSET,@sDate)
SET @bIsValidUMCDate = 1
END TRY
BEGIN CATCH
SET @bIsValidUMCDate = 0
SET @dUMCDate = NULL
END CATCH
SET @ERROR = @bIsValidUMCDate
SELECT
@sDate AS InputDate
,@dUMCDate AS ConvertedDate
,@ERROR AS IsValidUMCDate
END
GO
July 22, 2013 at 11:34 am
Michael Valentine Jones (7/22/2013)The CASE expression will verify that the data string is in YYYY-MM-DD format, and is a valid date. It will return a 1 if the date is valid and a 0 if it isn't.
selecta.DT,
IsDateValid =
case
when a.DT is null
then 0
when a.DT not like '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]'
then 0
when isdate(replace(a.DT,'-','')) <> 1
then 0
else 1
end
...
+1
This should be the easiest way and 100% accurate, since 'YYYYMMDD' is always correctly interpreted.
[As a very minor aside, I prefer the syntax:
'[0-9][0-9][0-9][0-9][-][0-1][0-9][-][0-3][0-9]'
because I believe it's a shade clearer, making it explicitly clear that the dashes chars and not range indicators.]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 22, 2013 at 12:37 pm
--this date MAY or MAY NOT be valid depending on the DATEFORMAT setting
SELECT ISDATE('29-12-2013')
--this is never valid (out-of-range)
SELECT ISDATE('32-12-2013')
--this should always be valid in all formats
SELECT ISDATE('2013-01-08 15:44:12.208')
--this is a valid UMC date but ISDATE = 0
SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30')
DECLARE @testdate VARCHAR(20)
SET @testdate = '29-12-2013' -- this is valid in DMY format
SET @testdate = '32-12-2013' -- this is never valid (out-of-range)
SET @testdate = '2013-01-08 15:44:12.208' -- this is valid
SET @testdate = '2013-01-08 15:44:12.2081606 +05:30' -- this is valid UMC date
--for these testdates using the script below,
--#1 is ambiguous, #2 is correct, #3 & #4 are INCORRECT
SELECT
@testdate
,IsDateValid = CASE WHEN @testdate IS NULL THEN 0
WHEN @testdate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' THEN 0
WHEN ISDATE(REPLACE(@testdate,'-','')) <> 1 THEN 0
ELSE 1
END
--this procedure returns the proper results
EXEC dbo.IsValidDate '29-12-2013','DMY'
EXEC dbo.IsValidDate '32-12-2013'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'
July 22, 2013 at 12:54 pm
Steven Willis (7/22/2013)
--this date MAY or MAY NOT be valid depending on the DATEFORMAT setting
SELECT ISDATE('29-12-2013')
--this is never valid (out-of-range)
SELECT ISDATE('32-12-2013')
--this should always be valid in all formats
SELECT ISDATE('2013-01-08 15:44:12.208')
--this is a valid UMC date but ISDATE = 0
SELECT ISDATE('2013-01-08 15:44:12.2081606 +05:30')
DECLARE @testdate VARCHAR(20)
SET @testdate = '29-12-2013' -- this is valid in DMY format
SET @testdate = '32-12-2013' -- this is never valid (out-of-range)
SET @testdate = '2013-01-08 15:44:12.208' -- this is valid
SET @testdate = '2013-01-08 15:44:12.2081606 +05:30' -- this is valid UMC date
--for these testdates using the script below,
--#1 is ambiguous, #2 is correct, #3 & #4 are INCORRECT
SELECT
@testdate
,IsDateValid = CASE WHEN @testdate IS NULL THEN 0
WHEN @testdate NOT LIKE '[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' THEN 0
WHEN ISDATE(REPLACE(@testdate,'-','')) <> 1 THEN 0
ELSE 1
END
--this procedure returns the proper results
EXEC dbo.IsValidDate '29-12-2013','DMY'
EXEC dbo.IsValidDate '32-12-2013'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.208'
EXEC dbo.IsValidDate '2013-01-08 15:44:12.2081606 +05:30'
The OP stated that the total date should be in format YYYY-MM-DD, so the test I gave them looks for exactly that, and is insensitive to the setting of DATEFORMAT.
From OP:
"Year should be in yyyy format ,month should be between 1 and 1 to 12 and date should be between 1 to 31 and total date should be YYYY-MM-DD Formate?"
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply