Microsoft SQL not converting text to DateTime before adding values.
Posted on 2011-02-26
I'm getting a type conversion error when calling a Microsoft Dynamics GP SQL Stored Procedure because it doesn't convert data type before adding.
The error is coming from a call like this inside the SQL stored procedure:
@date = CONVERT(char(10),@MinDate,102) + CONVERT(varchar(8),GETDATE(),108)
CONVERT(char(10),@MinDate,102) = ‘1900.01.01’
CONVERT(varchar(8),GETDATE(),108) = ‘04:41:52’
It’s concatinating the two text strings before converting to the DateTime data type like this:
@date = (‘1900.01.01’ + ‘04:41:52’)
You can get the proper result by changing processing order as follows:
((@date = ‘1900.01.01’) + ‘04:41:52’)
I’m assuming there must be a setting in SQL as there’s no way this call would be working in Dynamics GP.
Any help would be appreciated. The actual query and error are:
SET DATEFORMAT YMD
declare @enttme datetime = NULL
declare @MinDate datetime
/* sets the MinDate to the base date of 1900-01-01 */
exec smGetMinDate @MinDate output
if @enttme is NULL begin
select @enttme = CONVERT(char(10),@MinDate,102) + CONVERT(char(5),GETDATE(),108)
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.