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).
Comment
There are no comments made yet.
Ivan Smahin Accepted Answer
This is not a bug, but the feature:

http://valentina-db.com/docs/dokuwiki/v9/doku.php?id=valentina:vcomponents:vsql:reference:expr:funcs_string&;s[]=kdateformat&s[]=pattern#examples

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'
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
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) ?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Ok, it was the date format. After adjusting the format, it's working good.
Thank you.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
  • Page :
  • 1


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

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories