SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Validations?


Date Validations?

Author
Message
Anjan@Sql
Anjan@Sql
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1084 Visits: 1285
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?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16928 Visits: 19557
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?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4484 Visits: 3672
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 all select '2012-02-10'
union all select '2012-02-29'
union all select '2012-03-10'
union all select '2012-03-21'
union all select '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



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14712 Visits: 15980
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16928 Visits: 19557
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5166 Visits: 5478
Ananth@Sql (7/22/2013)
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?


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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Eugene Elutin
Eugene Elutin
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5166 Visits: 5478
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;

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5984 Visits: 11771
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

Steven Willis
Steven Willis
SSC Eights!
SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)SSC Eights! (859 reputation)

Group: General Forum Members
Points: 859 Visits: 1721
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




 
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8226 Visits: 7163
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.

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


...



+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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search