1. François Van Lerberghe
  2. Valentina Studio
  3. Monday, January 20 2020, 07:34 AM
  4.  Subscribe via email
Just to let you know there is a bug in the SQL FORMAT function :
The FORMAT function return wrong value with the date of the latest 2 days of the year (December 31 or December 30, any year) and the pattern 'kDateFormat_Pattern' : for the year 2019, the returned year is 2020 but it should be 2019.
(Perhaps the bug is in the icu library ?)

To reproduce,
- Open VStudio (any version, included the newest 9.8.2)
- Set date format to DDMMYYYY and "/" as delimiter
- type in the SQL pane :

SELECT YEAR(GETDATE('31/12/2019')) GoodYear, FORMAT(GETDATE('31/12/2019'), 'YYYY-MM-dd', 'kDateFormat_Pattern') BadFormatedDate

The result show : 2019 | 2020-12-31

Reported in Mantis (#0008716).
There are no comments made yet.
Ivan Smahin Accepted Answer
This is not a bug, but the feature:


Please, pay attention to the last example - the difference between "YYYY" and "yyyy" in the date-time patterns:

-- Pay attention to the difference between 'Y' and 'y' patterns.
-- 'Y' is a pattern for "year of "Week of Year"', but 'y' is for simply "Year".
-- Following US locale, "2019/12/28" is a last day for a 52nd week in 2019 year.
-- 29, 30 and 31 are both in the last (53) week and in the first week for 2019 and 2020 years respectively.
SELECT FORMAT('2019/12/31', 'YYYY', 'kDateFormat_Pattern')
=> '2020'
SELECT FORMAT('2019/12/31', 'yyyy', 'kDateFormat_Pattern')
=> '2019'
There are no comments made yet.
Mmmh, no, I don't get the good result.

SELECT FORMAT(GetDate('31/12/2019'), 'yyyy', 'kDateFormat_Pattern')

give me '2020'

(must use GETDATE in order to work with a date, not simply '31/12/2019')

Can you try it. Or have I something wrong with my VStudio (tried with 5.8.8, 9.5.3 and the latest 9.8.2) ?
There are no comments made yet.
Ivan Smahin Accepted Answer
That strange result may happens because of wrong db's DTFormat, please check it. I guess it is MMDDYYYY instead of DDMMYYYY.
Probably we need more checks for errors - I will try to do it soon.
There are no comments made yet.
Ok, it was the date format. After adjusting the format, it's working good.
Thank you.
There are no comments made yet.
  • Page :
  • 1

There are no replies made for this post yet.
However, you are not allowed to reply to this post.