Best method for varchar date validation in Sybase (T-SQL)?

Question

I have a stored procedure which takes as its parameter a varchar which needs to be cast as a datetime for later use:

SET @the_date = CAST(@date_string AS DATETIME)

I'm expecting the date string to be supplied in the format "DD-MON-YYYY", but in an effort to code defensively, if for some reason it can't be cast successfully, I want to default to the system date and continue. In PL/SQL I could use exception handling to achieve this and I could do this fairly easily with regular expressions too, but the limited pattern matching supported out of the box by Sybase doesn't let me do this and I can't rely on third party libraries or extensions. Is there a simple way of doing this in T-SQL?

NB: using Sybase ASE 12.5.3, there is no ISDATE function

Answer 1

Found this in the second result in Google when searching for "validate date string sql".

----Invalid date
SELECT ISDATE('30/2/2007')
RETURNS : 0 (Zero)
----Valid date
SELECT ISDATE('12/12/20007')
RETURNS : 1 (ONE)
----Invalid DataType
SELECT ISDATE('SQL')
RETURNS : 0 (Zero)

Answer 2

Make sure SQL Server knows the order of Days, Months and Years in your string by executing

SET DATEFORMAT mdy;

Answer 3

My goodness, if the question was about Microsoft SQL Server then we'd have been in business!

Sybase, sadly, is a whole 'nother database these days, since about 1997, in fact, give or take a year.

If the input format simply has to be 'DD-MON-YYYY' and no exceptions, then I think a fair amount of validation was be achieved by slicing the input using SUBSTR(), after first doing some simple things, such as checking length.

I thought that recent releases of Sybase (SQL Anywhere 11, for example) have regular expression support, however, although it's been a while since I've had to suffer T-SQL. Some googling leaves me in rather more doubt.

Answer 4

It seems you're going to be stuck rolling your own.

You could probably use this as a starting point.

Answer 5

I'm having a similar issue. You might be able to do something like this:

SET arithabort arith_overflow off
SET @the_date = CAST(@date_string AS DATETIME)
IF @the_date is NULL
    set @the_date = getdate()
SET arithabort arith_overflow on

However, this doesn't work well in a select. It will work well in a cursor (boo) or in logic before / after a SQL batch.

Answer 6

Did you try convert instead of cast?

select convert( datetime , @date_string )

Answer 7

Can't you do something like this:

SELECT @the_date = CASE @date_string
                      WHEN '[0-9][0-9]-[A-Z][A-Z][A-Z]-[0-9][0-9][0-9][0-9]'
                      THEN CONVERT(datetime, @date_string)
                      ELSE GETDATE()
                   END

?

Answer 8

I am really interested in answer given by Rebthor : " However, this doesn't work well in a select. It will work well in a cursor (boo) or in logic before / after a SQL batch. "

I confirm it does not work in a select. I just do not understand what you mean by a "logic before / after a SQL batch" What is a logic in sybase ?