Date format check

Question

Date format check

sqlenthu 89358

Hall of Fame

Points: 3069
More actions
November 12, 2017 at 10:29 pm

#324420

Hello all,
I have few records in a table. And I want to check if one of the string column starts with date in mm/dd/yyyy or mm.dd.yyyy or mm-dd-yyyy format only. No other date formats should be allowed.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sqlenthu 89358 Hall of Fame Points: 3069 More actions · Answer 1

sqlenthu 89358 - Sunday, November 12, 2017 10:29 PM
Hello all,
I have few records in a table. And I want to check if one of the string column starts with date in mm/dd/yyyy or mm.dd.yyyy or mm-dd-yyyy format only. No other date formats should be allowed.

Just to add I tried following:
Select * from tablename
Where substring(col1, 1, 10) like '[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]'

Here i just want to see how . and - can be introduced. Believe there should be some escaping done. Also it seems little dirty trick. Is there another approach?

TheCTEGuy Ten Centuries Points: 1032 More actions · Answer 2

sqlenthu 89358 - Sunday, November 12, 2017 10:29 PM
Hello all,
I have few records in a table. And I want to check if one of the string column starts with date in mm/dd/yyyy or mm.dd.yyyy or mm-dd-yyyy format only. No other date formats should be allowed.

Use the ESCAPE. Here is the link below. I have just added the ESCAPE and '//' to your query but beware as its accepting invalid dates to.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

declare @tab table (str varchar(50))

insert into @tab select '01/01/2017abcdef' union all select 'abc01/01/2017abcdef' union all select 'abcdef' union all select '05/31/2017abcdef' union all select '19/31/2017abcdef'

select substring(str,1,10) from @tab where str like '[0-1][0-9]//[0-3][0-9]//[1-2][0-9][0-9][0-9]%' escape '/'

/* Output

01/01/2017 05/31/2017 19/31/2017 --invalid

*/

First solve the problem then write the code !

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

sqlenthu 89358 - Sunday, November 12, 2017 10:29 PM
Hello all,
I have few records in a table. And I want to check if one of the string column starts with date in mm/dd/yyyy or mm.dd.yyyy or mm-dd-yyyy format only. No other date formats should be allowed.

Change the column to datetime (or similar) data type and stop storing dates as strings. Otherwise, you're going to run into problems with dates like 12/11/2017, which may look like it's stored in the correct format, but may not be the date you think it is.

John

Thom A SSC Guru Points: 99130 More actions · Answer 4

I have to ask, but why are you permitting dates to be stored as I string in the first place? They should be stored as a date or datetime(2). Storing dates as a string can present a world of problems.

For the moment, this should get you what you're after, however, I've intentionally put some a example where it'll fail (using dd/MM/yyyy). Using REGEX is still the best route. You can see what I've done is made the first LIKE use a wild card for where the / . or - should be. Then I have a second REGEX which checks for invalid character (which is why the one with #'s is picked up).

CREATE TABLE #Date (DateString varchar(25));
GO
INSERT INTO #Date (DateString)
VALUES ('01/03/2000'), --3rd January 2000 (MM/dd/yyyy)
   ('01/03/2000'), --1st march 2000 (dd/MM/yyyy); this won't error
    ('01/21/2000'), --21st January 2000
    ('21/01/2000'), --21st January 2000; This will error
    ('05 May 2000'),
    ('4 Apr 2000'),
    ('11 November 2000'),
    ('2007-01-04'),
    ('05.06.2000'),
    ('17-09-2000'),
    ('19#04#2000'),
    ('09-17-2000'),
    ('6/5/2000'),
    ('9/8/00'),
    ('02/07/00');
GO
SELECT *
FROM #Date;
GO
SELECT *
FROM #Date
WHERE DateString NOT LIKE '[01][0-9]_[0-3][0-9]_[12][0-9][0-9][0-9]'
 OR DateString LIKE '%[^0-9/\.-]%'; --\. as period (.) is a special character so we have to escape it
GO
DROP TABLE #Date;
GO

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

John Mitchell-245523 - Monday, November 13, 2017 3:27 AM
sqlenthu 89358 - Sunday, November 12, 2017 10:29 PM
Hello all,
I have few records in a table. And I want to check if one of the string column starts with date in mm/dd/yyyy or mm.dd.yyyy or mm-dd-yyyy format only. No other date formats should be allowed.
Change the column to datetime (or similar) data type and stop storing dates as strings. Otherwise, you're going to run into problems with dates like 12/11/2017, which may look like it's stored in the correct format, but may not be the date you think it is.
John

According to the original post, they'd have first determine if a date is present and then split it out. This is typical of some form of "memo" column. That doesn't make it right but it is typical of the type of stuff that apps generate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

SELECT * FROM #Date WHERE ISDATE(DateString) = 0 OR DateString NOT LIKE '__[/.-]__[/.-]____' OR SUBSTRING(DateString, 3, 1) <> SUBSTRING(DateString, 6, 1)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".