0

By accident I came across an to my knowledge undocumented feature. One of the queries contained an instruction to convert a string to a Sybase date type:

SELECT CONVERT(DATE, '.', 105)

This works as expected with values like '16-11-2017', but one of the values by accident was . (point). This resulted in the (to me) illogical result of '2017-10-31' in November, now in December the result is somehow '2017-11-30'.

What is the meaning of the point character and why is the result the last day of last month?

MeanGreen
  • 3,098
  • 5
  • 37
  • 63
  • You really need to give some more information about the dutch date format youre having issues with - if this assumes for example MM/DD/YYYY like US dates this can often be asking for trouble with some dates. I would always suggest using 'MMM DD YYYY' type strings (e.g. 'Dec 13 2017' type strings for dates so you have no ambiguity. – Rich Campbell Dec 13 '17 at 10:19
  • 1
    The problem is the string value . is converted to '2017-10-31', but I have no clue why. I've updated the question to make it more clear. – MeanGreen Dec 13 '17 at 11:34
  • When I try and pass a . to the same convert though it gets a syntax error: SELECT CONVERT(DATE, '.', 105) 2> Msg 249, Level 16, State 1: Server 'xxx', Line 1: Syntax error during explicit conversion of VARCHAR value '.' to a DATE field. Perhaps you have a default on the column which created the October date or something? – Rich Campbell Dec 13 '17 at 13:21
  • Ah just spotted the sybase-asa tag I'd checked in Sybase ASE which will probably return completely different results. I assume you are using Sybase Adaptive Anywhere which is a completely different product. – Rich Campbell Dec 13 '17 at 13:30

0 Answers0