Stored Procedure not always setting the time part of datetime field correctly

Transact-SQL
1

I have a field in our jobs table in the ERP system which gets updated by a stored procedure when the job record has been transmitted to our supplier by FTP. However, I have noticed that it is not always setting the time element correctly. It does so sometimes, but not every time. The date part is always being set, but the time element is needed so we can report on the suppliers SLA performance.

This is the stored procedure, which receives the record key of the job and then updates the Transmitted field (datetime) in the ERP jobs table. The transmission of jobs only happens in business hours - between 07.00 and 18.00 daily:

CREATE PROCEDURE [dbo].[FTPSuccess] 
@reckey VARCHAR(10)
AS
-- SET TRANSMITTED FLAG ON JOB
BEGIN
	UPDATE JobHdrProfiles
	SET TRANSMITTED = CAST(GETDATE() AS DATETIME)
	WHERE RECKEY = @reckey
END

And here is a sample of the results from 22/03/2023, as you can see while they were all dated correctly, some of them have the time element showing as 00:00:00.000:

+------------+-------------------------+
|   RECKEY   |       TRANSMITTED       |
+------------+-------------------------+
| 5001005413 | 2023-03-22 00:00:00.000 |
| 5001005424 | 2023-03-22 00:00:00.000 |
| 5001005458 | 2023-03-22 13:42:04.507 |
| 5001005462 | 2023-03-22 13:42:04.507 |
| 5001005467 | 2023-03-22 00:00:00.000 |
| 5001005478 | 2023-03-22 00:00:00.000 |
| 5001005490 | 2023-03-22 14:00:05.063 |
| 5001005493 | 2023-03-22 14:20:06.383 |
| 5001005513 | 2023-03-22 00:00:00.000 |
| 5001005518 | 2023-03-22 14:18:03.457 |
| 5001005519 | 2023-03-22 00:00:00.000 |
| 5001005527 | 2023-03-22 14:54:03.113 |
| 5001005533 | 2023-03-22 00:00:00.000 |
+------------+-------------------------+

Why would this be happening? And what can I do to correct it - I cannot change the field type in the ERP system?

Many thanks
Martyn

2

Based on the data and the stored procedure things looks ok to me. But this is only the update statment, maybe you need to look at the insert statement.

For example reckey 5001005413 is inserted with 00:00:00.000 and ftp failes, FTPSuccess will not be executed and the time will remain 00:00:00.000. It's just guessing from my side.

3

When a new job is inserted, the TRANSMITTED field is '1899-12-30 00:00:00.000', and there is a check made after the FTP transmission step. If the FTP has failed for any reason, the next step in the flow branches off and sends an email to the admins, and the FTPSuccess stored procedure is not called. The TRANSMITTED field remains at '1899-12-30 00:00:00.000' until the error is fixed and the job re-submitted.

4

Since GETDATE() is implicitly a datetime, I suggest getting rid of the CAST:

SET TRANSMITTED = GETDATE()

not sure if it will help, but it can't hurt.

5

Thanks Scott, I tried removing the cast, and the number of records with no time element actually increased over a 30 minute period. It is odd as it's not consistent.

7

There must be a trigger on the table(?) that is updating the time to 00:00:00.000?

8

There IS a trigger on that table but it's not referencing/updating that field as far as I can see. I'll dig a little deeper to be sure.