So it would seem that while there are numerous functions to extract parts of a
timestamp into it’s component parts, there is no function to create a date from
a piece of text, no date operators nor any date functions by which you can add
or subtract days or other component parts of a time stamp. In Oracle, for
example, you can do the following to create a date:
To_Date(‘2018-05-07’,’YYYY-MM-DD’)
or to subtract days:
SYSDATE -3 (you can also use fractions of days)
or to add months:
ADD_MONTHS(SYSDATE,2)
But 4D SQL has nothing like these. This seems like a big oversight.
> Date: Sat, 5 May 2018 03:28:29 +0000
> From: Keisuke Miyako <keisuke.miy...@4d.com>
> the error message seems to be in line with what is explain in the
> documentation:
>
>> Automatic data type conversion is implemented between numeric types.
>> A string that represents a number is not converted to a corresponding
>> number. There are special CAST functions that will convert values from one
>> type to another.
>
> http://doc.4d.com/4Dv16R6/4D/16-R6/4D-SQL-engine-implementation.300-3628406.en.html
>
> in other words, you can't expect 4D to implicitly convert numeric 2 to
> TIMESTAMP.
>
> besides, with an expression such as
> Select someDate - 2
> it is unclear whether you want to subtract 2 years, months, days, hours,
> minutes, second or milliseconds.
>
> in addition to that, there is no way to express 2 days as TIMESTAMP.
> this is mentioned in the same URL, that 4D SQL does not support date
> constants where the month or day is 0.
> in other words, you can't use { d '0000-00-02' } to specify 2 days.
> and in case you are wondering, you can't use {t '48:00:00'} either,
> because a time constant that exceeds 24 hours throws a parser error.
>
> even if you find a way to specify 2 days in a way that the parser can
> understand,
> there is the fundamental problem that arithmetic expressions between VK_TIME
> (TIMESTAMP) values are type unsafe in 4D SQL.
>
> so I think the best option is to use {FN () } with an "Add to date" wrapper
>
> http://doc.4d.com/4Dv16R6/4D/16-R6/4d-function-call.300-3628422.en.html
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ: http://lists.4d.com/faqnug.html
Archive: http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub: mailto:4d_tech-unsubscr...@lists.4d.com
**********************************************************************
