How to validate user input date formate as yyyy-MM-dd for created date ?

Question

1.00/5 (1 vote)

See more:

I work on sql server 2017 i need to validate user input of temp table

if formate of created date not YYYY-MM-DD then reject by update status field of temp

table to be Notvalid

and if formate of created date is YYYY-MM-DD then update status by Valid

 CREATE TABLE #TempPC
            (
              [ID] INT IDENTITY ,
              CreatedDate varchar(12),
              status varchar(200)
            )


        insert into #TempPC
(
           CreatedDate
 )

select '2022-05-09'
union
select '12-04-2022'

Expected result

 CreatedDate   Status
 2022-05-09     Valid
 12-04-2022     NotValid

What I have tried:

SELECT Createddate,CASE WHEN ISDATE(CreatedDate) = 1 
  AND CreatedDate LIKE '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]' 
  THEN 1 ELSE 0 END as datestatus into #wrongformate  from #TempPC;



  
  create index wrongformate_idx on #wrongformate(Createddate)include(datestatus)


   UPDATE  t
        SET     t.[status]=case when datestatus=1 then 'Valid' else 'Not Valid' end
        FROM    #TempPC t
		inner join #wrongformate w on t.CreatedDate=w.CreatedDate 
		where  t.status is null

Posted 10-May-22 14:45pm

ahmed_sa

Updated 10-May-22 19:13pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2022-05-10T15:49:00

Solution 1

Your problem isn't necessarily the validation. It's the fact that you're storing dates as a string in the database. That's NEVER a good idea.

Posted 10-May-22 15:49pm

Dave Kreskowiak

Comments

OriginalGriff 11-May-22 1:14am

:thumbsup:

OriginalGriff · Answer 2 · 2022-05-10T19:13:00

Dave is right:

Always store data in appropriate datatypes - and that means that only string based data gets stored in VARCHAR or NVARCHAR columns. If you don't do that the you get two problems:
1) Sort order doesn't work, which means that comparisons don't work either. You have noticed this, and this is not a situation that will get any better. Bodging round it is inefficient as well and code consuming.
2) At some point - an this may already have happened without your noticing - bad data will get entered. That may be an American format date as you show, or a European format 23/11/2016, or an ISO 2019/11/23, or just 01/02/03 which you cannot decide what date that is: 1st Feb 2003, 2nd Jan 2003, or 3rd Feb 2001? Or ... the guys name, "sdsdsds", or any other text. When that happens, either your database is invalid and requires significant human work to fix. And a single non-date data entry will make your bodged code crash ...

So stop trying to convert it, and change the DB, and the code that enters data to it: check and convert your dates in your presentation language, pass the converted data to SQL via a parameterised query, and store it in a DATE, DATETIME, or DATETIME2 column.
All these problems immediately go away.

Validating dates in particular should be done at presentation level so that you have access to the user culture and can convert whatever he enters correctly. The converted date then gets stored in a SQL date-based column, and everything becomes simpler to work with.