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

Validate a date held in a text field. Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 4:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:51 AM
Points: 155, Visits: 486

Our systems administrator has took it upon themselves to use a free txt field to house a data value.

This is all good and well if users enter the date in the right format, which of course they do not.

I need to come up with some way of listing all dates in said field that do not appear in the the following format via a SQL query.

Acceptable format = dd/mm/yyyy (ie 13/03/2013)

Any suggestions extremely welcome.

Thanks
Post #1434807
Posted Monday, March 25, 2013 4:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:44 AM
Points: 146, Visits: 282

It's pretty difficult to distinguish what part of the date corresponds to month or day or year, when you use free text entry, but a good place to start, if you're looking for a specific format like you listed, is to use the LEN() function.

In this case, you know the desired format is DD/MM/YYYY which constitutes a character length of 10, so you could use something like;


SELECT * FROM YOUR_TABLE
WHERE LEN(DATE_FIELD) = 10



**OR: WHERE LEN(DATE_FIELD) <> 10

You'll probably have to manually sort through the "exactly 10 character" dates and update any yourself which do not fit the criteria...
Post #1434812
Posted Monday, March 25, 2013 4:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:51 AM
Points: 155, Visits: 486
Thanks,

yeah the length function does work is some cases where users have made a typo.

There must be some way of splicing up the date so you can check to see if "dd" is between 1-31 and the "mm" is between 1 and 12 etc etc.

This way you could Id the dates that have been entered in the wrong format.

cheers
Post #1434817
Posted Monday, March 25, 2013 5:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:44 AM
Points: 146, Visits: 282
You can maybe look at building an array using the ISDATE() Function, or updating a flag field to return invalid entries...

i.e;


You can export the table to a test area, and then create a new "flag" field;

--SET LANGUAGE BRITISH
--SET DATEFORMAT DMY

UPDATE YOUR_TABLE
SET FLAG_FIELD =
(
CASE WHEN ISDATE(YOUR_FIELD) = 1
THEN 0
ELSE 1
END
)
Post #1434838
Posted Monday, March 25, 2013 5:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:24 PM
Points: 12,906, Visits: 31,984
I do things the same as McSQL, but one more thing i add is a check against a minimum date that is logical for the data on hand ; for example, the company I work at, in a certain table, would never have a date before the company existed; in another table, we logically would never have a date of birth for an employee over 100 years in the past.

so the case is just a two condition item to avoid logical bad dates:
CASE 
WHEN ISDATE(YOUR_FIELD) = 1 AND CONVERT(DATETIME,YOUR_FIELD,103) > '1989-04-15'
THEN 0
ELSE 1
END



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1434845
Posted Tuesday, March 26, 2013 5:23 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:51 AM
Points: 155, Visits: 486
Many Thanks

P
Post #1435395
Posted Tuesday, March 26, 2013 6:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:13 AM
Points: 1,629, Visits: 5,573
LoosinMaMind (3/25/2013)
There must be some way of splicing up the date so you can check to see if "dd" is between 1-31 and the "mm" is between 1 and 12 etc etc.

This way you could Id the dates that have been entered in the wrong format.


What if someone has entered 3rd August 2013 as MM/DD/YYYY? That comes out as 08/03/2013, which also corresponds to the perfectly valid date 8th March 2013 in DD/MM/YYYY format! Unless you have some sort of context for the date that you can cross-check you're not going to be able to catch instances like that.
Post #1435431
Posted Tuesday, March 26, 2013 9:58 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
Here's two procedures I use for very thorough data validation. These procedures are much more reliable than ISDATE because ISDATE may return different results when converting from a string depending on the DATETIME datatype being converted to.

In these procedures I use SMALLDATETIME to validate non-UMC dates and DATETIMEOFFSET to validate UMC dates. If you know that you will always be using DATETIME or DATETIME2 (or even just TIME) then you will want to use those types in the TRY block.

Also note the DATEFORMAT parameter (default DMY) which allows validation even if the date format is different than the server default. The first procedure is all you need to run, but it calls the second procedure internally. Lots of examples below.


CREATE PROCEDURE [dbo].[IsValidDate]

@sDate VARCHAR(50)
,@sDateFormat CHAR(3) = 'DMY' -- MDY, DMY, YMD, YDM, MYD, DYM

AS
BEGIN

/* This procedure requires procedure IsValidUMCDate */

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 using regular SMALLDATETIME datatype */

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


/*
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

*/

END






CREATE PROCEDURE [dbo].[IsValidUMCDate]

@sDate VARCHAR(50)

AS
BEGIN

SET NOCOUNT ON
SET DATEFORMAT DMY

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
END CATCH

SET @ERROR = @bIsValidUMCDate

SELECT
@sDate AS InputDate
,@dUMCDate AS ConvertedDate
,@ERROR AS IsValidUMCDate



/*
EXAMPLES:

EXEC dbo.IsValidUMCDate '01-07-2001' -- Valid date
EXEC dbo.IsValidUMCDate '1/7/2001' -- Valid date
EXEC dbo.IsValidUMCDate '07-01-2001' -- Valid date
EXEC dbo.IsValidUMCDate '7/1/2001 -- Valid date
EXEC dbo.IsValidUMCDate '29-12-2013' -- Valid date
EXEC dbo.IsValidUMCDate '32-12-2013' -- ERROR: Date out of range
EXEC dbo.IsValidUMCDate '29-02-2013' -- ERROR: Not a leap year
EXEC dbo.IsValidUMCDate 'xyz' -- ERROR: Invalid date
EXEC dbo.IsValidUMCDate '1234' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.000' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.208' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606' -- Valid date
EXEC dbo.IsValidUMCDate '2013-01-08 15:44:12.2081606 +05:30' -- Valid date


*/

END



 
Post #1435593
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse