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


Validate a date held in a text field.


Validate a date held in a text field.

Author
Message
LoosinMaMind
LoosinMaMind
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 565
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
McSQL
McSQL
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1296 Visits: 301
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...
LoosinMaMind
LoosinMaMind
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 565
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
McSQL
McSQL
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1296 Visits: 301
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
)
Lowell
Lowell
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75655 Visits: 40990
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
LoosinMaMind
LoosinMaMind
Say Hey Kid
Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)Say Hey Kid (670 reputation)

Group: General Forum Members
Points: 670 Visits: 565
Many Thanks

P
paul.knibbs
paul.knibbs
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4356 Visits: 6240
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.
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

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





 
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