Click here to Skip to main content
15,662,507 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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

              [ID] INT IDENTITY ,
              CreatedDate varchar(12),
              status varchar(200)

        insert into #TempPC

select '2022-05-09'
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
Updated 10-May-22 19:13pm

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.
Share this answer
OriginalGriff 11-May-22 1:14am    
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.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900