views:

3385

answers:

8

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

A: 

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)
Nick Berardi
IsDate isn't a valid function in Sybase
ninesided
How are you going to down vote me for something that you added in after the fact. And after I answered the question. The only identifier you had was T-SQL, which is most popularly used in Microsoft SQL Server, which does support ISDATE. Bad etiquette.
Nick Berardi
I thought the purpose of voting down was to indicate an answer that didn't help, helping answers that did help rise to the top, it wasn't meant as a slight
ninesided
can't revoke it unless you edit you post
ninesided
Well the purpose is to answer the post that was posted. I did that. And then you changed it. Not my fault.
Nick Berardi
A: 

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

SET DATEFORMAT mdy;
Marius
I'm not so worried about ordering of date parts, more about the validity of the date string itself. i.e. I don't want it to explode when you pass in '32-DEC-2009', I want it to default to the current system date.
ninesided
+1  A: 

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.

Mike Woodhouse
+1  A: 

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

You could probably use this as a starting point.

Stu
+1  A: 

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.

Rebthor
It works well enough for my needs, thank you!
ninesided
A: 

Did you try convert instead of cast?

select convert( datetime , @date_string )
Vincent Buck
yes, using convert makes no difference
ninesided
A: 

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

?

B0rG
that wouldn't ensure that what you're getting is a valid date though, I could pass 99-AAA-9999 to the function and it would pass this simple check. You can make it more sophisticated with a bigger case statement but when you start thinking about leap years and which months have 30/31 days it gets hard.
ninesided
agreed, it's just simple validation, not real IsDate(), if that suits you, you can try to convert the supplied text to datetime, and check, if the convert succeeded by checking @@error variable, but as I said, it's half-solution. cheers.
B0rG
A: 

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 ?

Tans