If I run select Isdate('20190001'); I get 1 (true) I have tried running with SET TEMPORARY OPTION DATE_ORDER = 'YMD'; and it still returns true

If I run select ymd(2019, 00, 01); I get 2018-12-01 which indicates 2019, 00, 01 is not a valid date!

following example here http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1520/html/iqrefbb/Isdate_misc_functions.htm

select Isdate('2019/00/01'); Still returns true

select Isdate('2019/01/00') correctly returns false

asked 15 Jan, 14:23

RoyBajaj's gravatar image

RoyBajaj
40116
accept rate: 0%

edited 15 Jan, 14:38


As Breck has pointed out, there is a change of behaviour in the isdate() and cast( ... as date ) functions in v17 and it is a bug. This will be fixed.

However, YMD() is functioning as expected. The v17 documentation (also v10, v11, etc) states that the year is adjusted if the month is out of range. (Note: the doc does not actually state how it is adjusted... that is left to the reader to figure out ;-)). The date is also adjusted accordingly if the day is out of range.

HTH

permanent link

answered 15 Jan, 15:26

Mark%20Culp's gravatar image

Mark Culp
24.2k9134287
accept rate: 40%

edited 15 Jan, 15:46

1

Did we forget to document the new "I know you meant 01 instead of 00 feature in version 17"? Oh well, it'll be fixed.

(15 Jan, 17:01) JBSchueler
1

> there is a change of behaviour

That's not a behavior change.

THIS is a behavior change...

(16 Jan, 11:22) Breck Carter

It's a bug in Version 17...

SELECT @@VERSION, ISDATE('2019-00-01');

@@VERSION,ISDATE('2019-00-01')
'9.0.2.2451',0

@@VERSION,ISDATE('2019-00-01')
'10.0.1.4310',0

@@VERSION,ISDATE('2019-00-01')
'11.0.1.3158',0

@@VERSION,ISDATE('2019-00-01')
'12.0.1.4231',0

@@VERSION,ISDATE('2019-00-01')
'16.0.0.2512',0

@@VERSION,ISDATE('2019-00-01')
'17.0.9.4882',1

permanent link

answered 15 Jan, 15:07

Breck%20Carter's gravatar image

Breck Carter
29.4k487653960
accept rate: 20%

edited 15 Jan, 15:15

Thanks Breck for your speedy response

(15 Jan, 15:16) RoyBajaj

Workaround

Compare(Dateformat(YMD(Substr(20190001, 1, 4), Substr(20190001, 5, 2), Substr(20190001,7, 2)), 'YYYYMMDD'), 20190001)

<> 0 --Invalid date

= 0 -- Valid date

(15 Jan, 15:22) RoyBajaj
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×145

question asked: 15 Jan, 14:23

question was seen: 319 times

last updated: 16 Jan, 11:22