Date CONVERT Errors

Question

Date CONVERT Errors

arrjay

Hall of Fame

Points: 3455
More actions
February 25, 2015 at 4:58 am

#301433

Hi there - I have a very strange error which keeps occurring whenever I run a convert statement.
Whenever I run my convert it works however whenever I run it whilst stating that the field I'm updating IS NULL it fails.
Please see below;
Error
Msg 241, Level 16, State 1, Line 19
Conversion failed when converting date and/or time from character string.
Successful code;
UPDATE tbl_ReportScheduleReportFilterValue
SET ComparisonValue = 'D"' + REPLACE(CONVERT(nvarchar(50), CONVERT(date, tbl_ReportScheduleReportFilterValue.ComparisonValue_vc, 103), 127) + 'T00:00:00.000Z','/','-') + '"'
FROM tbl_ReportScheduleReportFilterValue
JOIN ReportFilter ON ReportFilter.ID = tbl_ReportScheduleReportFilterValue.ReportFilterID
JOIN tbl_ReportField ON tbl_ReportField.ID = ReportFilter.ReportFieldID
AND tbl_ReportField.Name = 'User.LeavingDate'
AND ComparisonValue_vc IS NOT NULL
AND LEN(LTRIM(RTRIM(ComparisonValue_vc))) > 0
Failure code
UPDATE tbl_ReportScheduleReportFilterValue
SET ComparisonValue = 'D"' + REPLACE(CONVERT(nvarchar(50), CONVERT(date, tbl_ReportScheduleReportFilterValue.ComparisonValue_vc, 103), 127) + 'T00:00:00.000Z','/','-') + '"'
FROM tbl_ReportScheduleReportFilterValue
JOIN ReportFilter ON ReportFilter.ID = tbl_ReportScheduleReportFilterValue.ReportFilterID
JOIN tbl_ReportField ON tbl_ReportField.ID = ReportFilter.ReportFieldID
AND tbl_ReportField.Name = 'User.LeavingDate'
AND ComparisonValue_vc IS NOT NULL
AND LEN(LTRIM(RTRIM(ComparisonValue_vc))) > 0
AND ComparisonValue IS NULL
Please note the only difference is the last line of code; AND ComparisonValue IS NULL
Any ideas why a convert would work when populating a field, but won't work when attempting to populate the field when the field is NULL
Thanks!
--------------------------------------------
Laughing in the face of contention...

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

arrjay Hall of Fame Points: 3455 More actions · Answer 1

Even stranger... if I perform a SELECT it works

SELECT tbl_ReportScheduleReportFilterValue.ComparisonValue_vc, 'D"' + REPLACE(CONVERT(nvarchar(50), CONVERT(date, tbl_ReportScheduleReportFilterValue.ComparisonValue_vc, 103), 127) + 'T00:00:00.000Z','/','-') + '"'

FROM tbl_ReportScheduleReportFilterValue

JOIN ReportFilter ON ReportFilter.ID = tbl_ReportScheduleReportFilterValue.ReportFilterID

JOIN tbl_ReportField ON tbl_ReportField.ID = ReportFilter.ReportFieldID

AND tbl_ReportField.Name = 'User.LeavingDate'

AND ComparisonValue_vc IS NOT NULL

AND LEN(LTRIM(RTRIM(ComparisonValue_vc))) > 0

AND ComparisonValue IS NULL

But when I try and order by the first field it fails;