2

(title edited) Good afternoon, all!

Using IBM i version 7.1 and looking to calculate difference between two dates in a query. Since nothing is ever easy, one date is in CYYMMDD format, the other (curdate()) is YYYY-MM-DD. I tried to CAST my CYYMMDD formatted date (field name APENGD) as a varchar(10) then wrapped that in a CAST as a date (since decimals can't be CASTed as dates):

Cast(Cast(APENGD + 19000000 As varchar(10)) As date) As math

but I only see a result ++++++++++++++ for whatever reason. I was able to test a few different versions of this and found I can't use DATE anywhere...can anyone suggest an alternative??

Thanks in advance!

Matt

nik_m
  • 11,825
  • 4
  • 43
  • 57
user3593083
  • 100
  • 1
  • 10
  • How are you planning on calculating the difference on DB2? There aren't any good built-in functions I'm aware of, and [`TIMESTAMPDIFF` is almost useless](http://stackoverflow.com/questions/7677529/db2-timestampdiff-function-returning-unexpected-results).(because of the preceding date math). Additionally, using a difference between columns often results in indices being ignored (because it requires per-row-evaluation info) - what are you planning to do with the difference? – Clockwork-Muse Jul 12 '16 at 12:33

3 Answers3

3

casting varchar to date only works when the string includes separators.

At 7.1 you could use TIMESTAMP_FORMAT(), but you'd end up with a timestamp instead of just a date. But that's easily dealt with.

Date(Timestamp_format(char(APENGD + 19000000),'YYYYMMDD')) As math

My prefered solution when dealing with numeric/character value dates is creating a User Defined Function to handle conversion.

You could write your own, or use the one I do. iDate written by Alan Campin. Then your code would simple be:

   idate(APENGD,'*CYMD') as nath

Note that if you're trying to use date differences in a WHERE clause, like so

WHERE CURRENT_DATE - 3 months <= idate(APENGD,'*CYMD')

The above will perform poorly since an existing index over APENGD can't be used (directly). Assuming a recent(6.1+) version of the OS, you can create a new index that includes the expression you're using to convert APENGD to date.

Or you could code it using the Date->Numeric function ConvertToIdate that Alan helpfully includes. That would allow existing indexes to be used.

WHERE ConvertToiDate(CURRENT_DATE - 3 months,'*CYMD') <= APENGD
Charles
  • 21,637
  • 1
  • 20
  • 44
  • Thank you, Charles! the TIMESTAMP_FORMAT solution works perfectly. – user3593083 Jul 06 '16 at 22:58
  • The WHERE clause might be made to perform better with an INDEX over an _expression_ that converts APENGD to a DATE form. Since i 6.1, _expressions_ are allowed for INDEXes. Take experimenting to figure a good one. – user2338816 Jul 08 '16 at 03:40
1

The DDL was not offered [to define the column APENGD]. No matter, as the following should suffice, mostly irrespective the definition; either as a string or as a zero-scale numeric. The effect depends on the SQL recognition of a 14-character [up to 26-character, since some v7 release] character-string as an unformatted [i.e. lacking any delimiters, thus digits-only] TIMESTAMP representation:

date(timestamp((APENGD + 19000000) concat '000000'))

IBM i 7.3->Database->Reference->SQL reference->Language elements->Data types->Datetime values->String representations of datetime values->Timestamp strings

A string representation of a timestamp is a character or a Unicode graphic string that starts with a digit and has a length of at least 14 characters. …

CRPence
  • 1,259
  • 7
  • 12
0

If you want calculate difference between 2 dates, you can use:

`TIMESTAMPDIFF(32, cast(MYTIMESTAMP1 - MYTIMESTAMP2 as char(22)))`

The first argument of function specify the type of result.

1 : millisecond 16 : days 2 : second 32 : week 4 : minutes 64 : month 8 : hour 128 : trimester 256 : Year

Esperento57
  • 16,521
  • 3
  • 39
  • 45
  • Please, never use `TIMESTAMPDIFF` (or really, the timestamp subtraction): It calculates results based off of estimates, which will throw results off when you least expect it, as [I explain here](http://stackoverflow.com/questions/7677529/db2-timestampdiff-function-returning-unexpected-results). – Clockwork-Muse Jul 12 '16 at 12:23
  • i dont knew that, thak you ;) – Esperento57 Jul 13 '16 at 05:21