1. François Van Lerberghe
  2. Valentina Studio
  3. Montag, Januar 20 2020, 07:34 AM
  4.  Abonnieren via E-Mail
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).
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
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'
Kommentar
There are no comments made yet.
François Van Lerberghe Akzeptierte Antwort
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) ?
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
François Van Lerberghe Akzeptierte Antwort
Ok, it was the date format. After adjusting the format, it's working good.
Thank you.
Kommentar
There are no comments made yet.
  • Seite :
  • 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