March 25, 2013 at 4:22 am
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
March 25, 2013 at 4:35 am
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...
March 25, 2013 at 4:47 am
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
March 25, 2013 at 5:33 am
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
)
March 25, 2013 at 5:56 am
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
March 26, 2013 at 5:23 am
Many Thanks
P
March 26, 2013 at 6:33 am
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.
March 26, 2013 at 9:58 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply