I have a problem working with the date ...

Applications Control
#1

Hi,

sometimes working with the date makes me nuts. I have a problem with dates in a control:

1.) control-app with two date fields. Both set on system date. One multiline-text field, empty.
2.) after changing the dates, I want to call mysql with a select command. The select command is working fine, tested in SQL-builder and mysqladmin.
3.) PROBLEM, the date fields return numbers, I do not know how to convert to the mySQL Date field.

Example:
a) Set a date-field, lets call it {a}, to 15 May 2012, in my german enviroment: 15.05.2012
b) echo {a}; returns 125215
c) echo sc_date_conv({a}, “AAAAMMDD”, “aaaa-mm-dd”); returns 1252-15-
d) echo sc_date_conv({a}, “ddmmyyyy”, “db_format”); returns 15-52-12

I would need a return value of 2012-05-15. Whats going on, where is my mistake, what have I to change?

Please help me, or I become insane … :wink:

#2

Re: I have a problem working with the date …

Ok, here we go!

The problem is that MySQL uses a string in the format of ‘YYYY-mm-dd’ do store Information of type DATE and php uses the unix timestamp for all it’s functionality, which is an integer number of seconds elapsed since January 1st 1970.

Great! Isn’t it? So, if we want to do something with date or time we constantly have to convert between those two formats. I love it! :wink:

For those who find it hard to make a decision, there are multiple options. Three to be precise. (Without the SC macros)

  1. Use the php functions date() and strtotime().
    $mysqldate = date( ‘Y-m-d H:i:s’, $phpdate );
    $phpdate = strtotime( $mysqldate );

  2. Let MySQL do the work with UNIX_TIMESTAMP and FROM_UNIXTIME
    $query = “UPDATE table SET datetimefield = FROM_UNIXTIME($phpdate) WHERE…”;
    $query = “SELECT UNIX_TIMESTAMP(datetimefield) FROM table WHERE…”;

  3. Use the unix timestamp everywhere and use an integer field in MySQL to store the information.
    However, using this option means you loose a lot of the (date)-functionality of MySQL, simply because it doesn’t know that it is a date.
    So basically this makes only sense if you do the majority of your date calculations in php.

My advice: Chose one and stick to it.

Hope this helps a little.

Viel Spa?
jsb

#3

Re: I have a problem working with the date …

OK, that is nothing new at the end, but “125215” is not the timestamp of 15.05.2012.

I have a date-field, connected to a database, where it works fine. Here it does not.
BTW, here I had to go from the timestamp to the string, which should work with:
echo strftime("%Y-%m-%d", {a});
Of course if I believe to the timestamp of 125215 I would be one year and some month tomorrow “1970-01-02”

-------------confused-------------

#4

Re: I have a problem working with the date …

Jetzt d?mmert’s…
Ok, I can faintly remember having the same problem in a control application by pushing the field in a global variable and when using it again it was totally screwed up like yours.
I got it straightened out by doing [v_date]=sc_date_conv({duedate},“dd/mm/aaaa”,“Db_format”);
Using the separator ( / ) in the format string seemed to do the trick.

That’s all I have left to contribute.

jsb

#5

Re: I have a problem working with the date …

sch?n w?rs gewesen, but unfortunally it does not work …

I already thought that the “/” have more magical as they look like, but it does not work :frowning:

echo sc_date_conv({a}, “dd/mm/aaaa”, “db_format”); returns 16-52-12 (date to conv I took May 16th 2012)

und jetzt wirds richtig lustig:

I tried something different to test if it is a behaviour just with control apps or not. I took a form, put in a new date field, which is not assigned to the database and tested it. Same behaviour like in controls, let me call it unusable.
I took an assigned date field out of the database, lets call it {a1} and it works:
echo {a1}; returns 2012-05-16
echo sc_date_conv({von}, “aaaa/mm/dd”, “db_format”); returns 2012-05-16

#6

Re: I have a problem working with the date …

Glad it works for you now.
As I told you before I had exactly the same problem. Totally unrelated date field in a control went havoc but somehow got it working.
Now the strange thing is I tried as hard as I could but was unable to replicate this behaviour.
So we have to leave it as it is, I guess.

Edit:

Whoa, got it. Finally!
Go to the field attributes and in the block “Values Format” clear the field “Internal Format”.
Makes sense because it isn’t related to anything.

jsb

#7

Re: I have a problem working with the date …

unglaublich …

You found it. But I would classify that as a bug. This field HAS to be clear without assigned DB, otherwise it makes
the one in front of the screen mad …

1.000 THANK YOU