2. PHPRunner General questions
  3. [SOLVED] SQL Server Time VS DateTime Issues
This topic is locked
[SOLVED]

SQL Server Time VS DateTime Issues

 		10/22/2024 3:45:28 PM
PHPRunner General questions
C
 copper21 author

I am having some issues with PHPR V11. I am using SQL 2022 and have a field called "TimeReleased" and is set to the "time" data type. I noticed that when I go to edit the record, the "TimeReleased" field is blank, although there is a time in the database.

I then remember having this issue with previous versions of PHPR and then changed the datatype to datetime. I can now get a time to show up when I edit, but it now is always 6:00pm and not the time that is stored in the DB. I syncronized the database. I have the "edit as" set to validate as time, required field, and using time picker. I have the "view as" set to time, showing just time of day.

I am also not able to update the TimeReleased field using this code in the after record added event: If the field is left blank, the field "TimeReleased" is not getting updated.


if($values['TimeReleased'])
{
$RT = date("g:i a", strtotime($values['TimeReleased']));
}
else
{
$RT = date("g:i a", strtotime(now()));

//Update Record In Table
$data = array();
$keyvalues = array();
$data["TimeReleased"] = $RT;
$keyvalues["TopicID"] = $values['TopicID'];
DB::Update("TA_Topics", $data, $keyvalues );
}

Bug?

Thanks,

Brian
C
 		copper21 author 10/22/2024

Just for kicks, I switched back to "time" data type and now the insert is working. However, when I go to edit the record, the time still isn't showing up.

time data type -> inserts work, but time does not show when editing the record

datetime data type-> insert does not work, but the time does show up when editing the record, but always shows 6:00 pm, regarless of what time is in the db.
Sergey Kornilov admin 10/22/2024

In yur code curly braces are miplaced. I'm sure that the closing curly brace is meant to be before the following line:
//Update Record In Table

You can also enable SQL debugging to see the actual SQL query printed and troubleshooting it by running it against your database manually.

C
 		copper21 author 10/22/2024

Sergey,

Thank you for the reply. The update will occur if the TimeRelease field is not populated, so that is why I put it in the brackets.

Just for grins, I put the code outside of the brackets, and it still did not update the field with the data type datetime.

The above code works fine with the data type "time", but not the data type "datetime".

I have now enabled SQL debugging and will see where that takes me.

Brian
C
 		copper21 author 10/23/2024

Solved the issue...although not sure why I had to do this for it to work.

I had to cast the field as a varchar in the SQL query.

CAST(TimeReleased AS varchar(8)) AS TimeReleased,