Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Date Validations? Expand / Collapse
Author
Message
Posted Monday, July 22, 2013 3:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 526, Visits: 605
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?
Post #1475911
Posted Monday, July 22, 2013 4:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Post #1475920
Posted Monday, July 22, 2013 4:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:58 PM
Points: 2,290, Visits: 2,752
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’! **
Post #1475924
Posted Monday, July 22, 2013 4:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 5,369, Visits: 9,898
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
Post #1475929
Posted Monday, July 22, 2013 4:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Post #1475931
Posted Monday, July 22, 2013 5:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 2,852, Visits: 5,107
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1475962
Posted Monday, July 22, 2013 5:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 2,852, Visits: 5,107
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1475966
Posted Monday, July 22, 2013 8:42 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 3,136, Visits: 11,493
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
Post #1476041
Posted Monday, July 22, 2013 9:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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


 
Post #1476090
Posted Monday, July 22, 2013 11:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:13 PM
Points: 2,087, Visits: 3,137
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)

"While in these days of quiet desperation /
As I wander through the world in which I live /
I search everywhere for some new inspiration /
But it's more than cold reality can give /
If I need a cause for celebration /
Or a comfort I can use to ease my mind /
I rely on my imagination /
And I dream of an imaginary time" : the inimitable Mr. Billy Joel
Post #1476164
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse