search between dates

PHPMaker General Discussion (PHPMaker)
1

HI
I have this :
From_UnixTime(bug.erstelltDatum, ‘%d.%m.%Y’) AS erstelltAm,

Format in View Field dmY
Date/time Picker

But if I try to do a search it only finds the day (not month or year)
between 2 Dates 28.04.2020 - 29.04.2020 result shows data from 29.04.2020 & 28.04.2020 AND! 28.03.2020,28.02.2020 …

any help would be very nice
THX
david

2

dh1340 wrote:

I have this :
From_UnixTime(bug.erstelltDatum, ‘%d.%m.%Y’) AS erstelltAm,

I believe in that case you search string, not datetime. There is no need to convert, to format timestamp field you may just use the date/time format in PHPMaker.

3

thx
no luck so far

From_UnixTime(bug.erstelltDatum, ‘%d.%m.%Y’) AS erstelltAm,
30.04.2020 (orig value=1588250446) BIGINT - is found on search = but search >= or between

bug.erstelltDatum - even with Date Format dmY only shows me :value=1588250446
any more ideas ?

4

What is the data type of erstelltDatum? Is it TIMESTAMP? (Do not use INT.)

5

in the original table it is BIGINT /20

in the custom view :
From_UnixTime(bug.erstelltDatum, ‘%d.%m.%Y’) AS erstelltAm, (VARCHAR)

30.04.2020 (orig value=1588250446) BIGINT - is found on search = but search >= or between

6

arbei wrote:

(Do not use INT.)

7

arbei wrote:

arbei wrote:

(Do not use INT.)


Well and thats not possible, cant change the basic table - not allowed :wink:
it is a quite big system, which runs there - and I just need to make my own special views as and add on - only data reading

any idea how to get this goin ? transform this INT to a format which is usefull…
THX

8

You may try to convert the data type by CONVERT(FROM_UNIXTIME(myfield), DATETIME) and see if it works.

9

arbei wrote:

You may try to convert the data type by CONVERT(FROM_UNIXTIME(myfield), DATETIME) and see if it works.

BIG THX →

CONVERT(FROM_UNIXTIME(myfield),> DATE)

works perfect