CODEWITHSUNDEEP
sqlsql-servert-sqldate
George Menoutis
George Menoutis

Reputation: 7250

SQL Find/Ignore invalid date

I am using SQL Server.

From each row, I take day and month values from the fields c.daybirth,c.monthbirth and the year from getdate(), and I want to have a field that shows if this date is valid or not (invalid example: 31 February)

I have created this solution:

case day(dateadd(month,c.monthbirth-1,dateadd(day,c.daybirth-1,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)))) when c.daybirth then 1 else 0 end

which works, but I find it hard to read. Is there a smarter alternative?

Upvotes: 1

Views: 1485

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

In SQL Server 2012+, you can do:

where try_convert(date,
                  datefromparts(year(getdate()), c.monthbirth, c.daybirth)
                 ) is not null

EDIT:

Amusing. This is better:

where try_convert(date,
                  cast(year(getdate()) * 10000 + c.monthbirth * 100 + c.daybirth as varchar(255))
                 ) is not null

Upvotes: 1

Related Questions