Valid Date Verification in SQL

Valid date verification in SQL

For your specific question, the following will return bad rows:

select * 
from table 
where substr(lossdate,5,2) in ('04','06','09','11')
    and substr(lossdate,7,2) > '30'

Depending on your input interface, you may need to repeat that for months with 31 days that show values greater than 31.

select * 
from table 
where substr(lossdate,5,2) in ('01','03','07','08','10','12')  
    and substr(lossdate,7,2) > '31'

Feb is a little trickier. Start incrementally, identifying all February entries with more than 29 days, you can get rid of/fix those immediately.

select * 
from table 
where substr(lossdate,5,2) = '02' 
    and substr(lossdate,7,2) > '29'

Then you need to repeat this for the remaining rows with Feb dates greater than 28 and aren't a leap year. In the last 100 years, any year divisible by 4 is a leap year, so you can identify the remainder with this (assuming you corrected/deleted the bad Feb entries you already found):

select * 
from table 
where substr(lossdate,5,2) = '02' 
    and mod(substr(lossdate,1,4),4)) <> 0 
    and substr(lossdate,7,2) > '28' 

How to Check Valid Date Time from Sql Table

Select *
From Table
Where
  ( '2016-05-19 04:23:00.000' <= dateColumn )
  And ( dateColumn < '2016-05-19 04:50:00.000' )

Check if varchar input is a valid date

I rewrote your script, try this instead:

DECLARE @TargetDate CHAR(10) = '2014-12-29'

IF @TargetDate is NULL 
   SELECT '(TargetDate is null)'
ELSE
BEGIN TRY
  SELECT MIN(Date_Date)
  FROM dbo.Dates
  WHERE 
    -- choose the format you need in convert. I used 126 which is isoformat
    Date_Date >= convert(datetime, @TargetDate, 126) AND
    Is_WorkingDay != 0
END TRY
BEGIN CATCH
   SELECT '(TargetDate is invalid)'
END CATCH

Check if value is date and convert it

use isdate function
like ..

  (CASE WHEN ISDATE (invoice_date) = 1 
             THEN convert(datetime, cast([invoice_date] as char(8)))
             END) AS Invoice_Date

SQL Server : How to validate for yyyymmddhhmmss

Just saying you could trim down string manipulation using STUFF()

Example

Declare @S varchar(50)='20210622232715'

Select try_convert(datetime,stuff(stuff(stuff(@S,13,0,':'),11,0,':'),9,0,' '))

Results

2021-06-22 23:27:15.000

---

Related Topics