Sql saying dates are not valid

1

Hi All,

I keep getting a out of range error when trying to run a query, and have looked online and they advise checking my dates using an isdate

So I have written the following

select
AS_Asset.AliveDate
,convert (varchar,DateAdd("yyyy",25,AS_Asset.AliveDate),103)
from AS_Asset

where isdate(
convert (varchar,DateAdd("yyyy",25,AS_Asset.AliveDate),103)) = 0

and indeed I am getting data coming back which seems to indicate they are not dates...

However I cannot see any issue with the dates...the dates exist...

For instance one example is

Alive Date Converted Date
15/05/2004 00:00:00 15/05/2029

The 15th May 2029 is a Tuesday and seems a normal day.

Can you tell me what I am doing wrong?

Thanks,.

David.

2

Avoid converting a date to Text to then try to manipulate it

If you want to know if AS_Asset.AliveDate plus 25 years is "this year" that should be done using some Maths, not a String comparison.

Two thigns troublesome about the String method:

Unless you use ISO date format then SQL's parsing of the string-date, back into a Date or DateTime datatype, is subject to rules which are heavily dependent on both the server settings and also the properties of the currently connected user - the LANUGAGE of the currently connected user, for example, will change how SQL parses that string. Thus "Avoid"

secondly, performance will be awful. Won't matter for a handful of records, but won't scale (so a risk that "its OK in testing" but then too slow in Production. I advise making sure that WHERE clause is SARGable - so that SQL will use an Index if it can - and that means avoiding comparison of objects that have meen manipulated by a function.

If you want "Value + 25 years is This year" you basically need

WHERE     AS_Asset.AliveDate >= DATEADD(Year, -25, '20170101')
      AND AS_Asset.AliveDate <  DATEADD(Year, -25, '20180101')

SQL will use an index, if available, to solve that very efficiently - the fact that there is a Function which resolved to a CONST is fine.

Its easy enough to use a function to calculate 01-Jan 25 years ago, as your start point, and 01-Jan 24 years ago (as your non-inclusive endpoint), based on today's date (or something else) if that's what you need. If you need help with that pls ask