Date and Time storage in DB

Applications Forms
#1

Anyone have a clue how to get the date and time field information into the database?

I have a field that I set up as date and time.

However, nothing gets stored when I fill it out.

Any thoughts?

I have it set for mm/dd/yyyy hh:mm and the internal the same.

I’ve tried datetime and varchar on the db field to no avail.

Thanks!

#2

Re: Date and Time storage in DB

Are you trying to update with an SQL statement, or from SC5 field properties?

Regards,
Scott.

#3

Re: Date and Time storage in DB

To elaborate on this: Form Settings / Field:

General Settings: Initial Value: System Date

There is also an auto-update option for the field:
Field/Database Value: Updated Field

SQL:
$sql = ‘UPDATE table SET’;
$sql .=’ date_field = “’.date(‘Y-m-d H:i:s’).’”’; // or use_current_timestamp();

Regards,
Scott.

#4

Re: Date and Time storage in DB

I haven’t even been able to insert items with dates, let alone update them.

My set up:

I have a form that I added a custom field to for date/time.
The date/time is not stored in the table the form is based on.
So I have a onAfterUpdate event that is supposed to be inputted in the secondary table.
Other field values are input just fine in the secondary table but not the date/time form field.

sc_exec_sql("
	INSERT INTO 
		shipping
		(inventory_id, shipping_time, tracking_number)
	VALUES
		('{inventory_id}', '{ship_time}', '{shipping_number}')

");

The inventory and shipping_number form fields are passed just fine but the ship_time is not.
What is the best way to set this up in the form?
P.S. The user is inputting the ship_time into the form.

Here are my settings:
http://globalsnr.com/ship_time.jpg

#5

Re: Date and Time storage in DB

I was unable to look at your jpg.

It looks right, unless there is an sql problem.

try:
$sql = “INSERT INTO shipping”;
$sql .= " (inventory_id, shipping_time, tracking_number)";
$sql .= " VALUES (’{inventory_id}’, ‘{ship_time}’, ‘{shipping_number}’)";

// sc_sql_exec($sql); // commented out

$echo $sql;
die(’.’);

This will echo the sql statement to the screen. See if that output will work if you paste it into your db manager as a manual sql statement.
Perhaps it is a quote/formatting issue.

Regards,
Scott.

#6

Re: Date and Time storage in DB

ok so I had to capture it into the sql like this:


$my_time = {date_time};

sc_exec_sql("
UPDATE shipping
SET shipping_time = '".$my_time."'
WHERE inventory_id = {inventory_id}
");

However, now I can’t get it to pull the information when I refresh the form… :frowning:

#7

Re: Date and Time storage in DB

OK,
so I have a very ugly work around but it works…

For some reason, I can’t just use the the grid_lookup function to pull the data in a datetime field…

So, I created a datetime field (text) in the grid and pulled it from the database, then passed it as a variable to the form.

Like I said, not pretty but it works.

If anyone else has other solutions, feel free to post them.