Error "Conversion Failed when Converting Date and/or Time from Character String" When Publishing Project In Microsoft SQL Server (Doc ID 2902554.1)

Last updated on OCTOBER 16, 2022

Applies to:

Symptoms

When attempting to publish project(s),
the following errors are printed to the publication log.

ERROR
-----------------------
[SERVICE]:[PxService]: BEGIN: Px Service Job
[SERVICE]:[PxService]: Job Name: PX Project XXX
[SERVICE]:[PxService]: Project Id: XXX

Severe: [TASK]:[ProjectIssue]: [2] PRE(SQL): Errors occurred when executing following statement:
set nocount on
declare @v_source_name varchar(255)
declare @msg varchar(2000)
declare @vspid int
declare @vtotal integer, @vins integer, @vupd integer;
declare @vsec decimal(20,6), @vrate decimal(20,6)
declare @start_time datetime, @end_time datetime
declare @vmsg varchar(1000), @vmsg_cnt int
declare @vlast_update varchar(1000), @vproj_id int
declare @safety_date datetime
declare @sSQL nvarchar(500)
declare @ParmDefinition nvarchar(500)

set @vtotal = 0
set @vins = 0
set @vupd = 0
set @vmsg_cnt = 0
BEGIN
set @vspid = @@spid
delete from wrk_log_results where spid = @vspid;

set @sSQL = ?;
SET @ParmDefinition = N'@retvalOUT varchar(500) OUTPUT'
EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@vlast_update OUTPUT;

set @safety_date = convert(datetime,@vlast_update,120);
if @safety_date is null
set @safety_date = convert(datetime,'1970-01-01 00:00:00',120)

......

set @vsec = DATEDIFF(second,@start_time,@end_time)
if @vsec > 0
set @vrate = round( @vtotal / @vsec , 3);
else
set @vrate = 0;

set @vmsg = 'Success[true],'
+' Start[' + convert(varchar(20),@start_time,120) + '], End[' + convert(varchar(20),@end_time,120) +'],'
+ ' Elapsed[' + cast(@vsec as varchar(20)) + 's],'
+ ' Count[' + cast(@vtotal as varchar(10)) +'],'
+ ' Throughput[' + cast(@vrate as varchar(20)) + '/s],'
+ ' Pages[1],'
+ ' Inserted[' + cast(@vins as varchar(10)) +'],'
+ ' Updated[' + cast(@vupd as varchar(10)) +']'
;
set @vmsg_cnt = @vmsg_cnt + 1
insert into wrk_log_results ( spid, seq_num, log_message) values (@vspid,@vmsg_cnt,@vmsg);
END TRY
BEGIN CATCH
set @vmsg = 'Success[false]'
set @vmsg_cnt = @vmsg_cnt + 1
insert into wrk_log_results ( spid, seq_num, log_message) values (@vspid,@vmsg_cnt,@vmsg);
END CATCH
END;

Severe: 2022-09-06 10:06:16:[TASK]:[ProjectIssue]: [2] PRE(SQL): 'Conversion Failed when Converting Date and/or Time from Character String' Error
com.microsoft.sqlserver.jdbc.SQLServerException: 'Conversion Failed when Converting Date and/or Time from Character String'
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
...

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1.Log on P6 and publish a project.
2.The job fails with above error.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.