Eliminate a function by writing out the code is now throwing an error

Question

Post reply

Eliminate a function by writing out the code is now throwing an error

stevec883

Mr or Mrs. 500

Points: 500

March 19, 2025 at 3:24 pm

Go to Answer

#4563026

/* I'm trying to eliminate (write out) this function 'largest_date', but SQL Server 2016 is telling me implicit conversion from datetime2 to float can not be done. Is there a way I can get around this, while still ellimating (writing out) the function? 

dbo.to_numb is just another function, shouldn't effect the CASE
this query works when I put the 'largest_date' function back using the statements as parameters
Here is the working code with function, before I rewrite it. */

/* SELECT @REG = CONVERT(datetime2, dbo.to_numb(CAST(largest_date(
isnull(USERS.MRATT, CONVERT(datetime2, '20000101', 112)), --(@var1 parameter for largest_date)
ISNULL(USERS.NETXY, CONVERT(datetime2, '20000101', 112))) --(@var2 parameter for largest_date)
AS float(53)), 'YYYYMMDD'), 112) */

--This is the function I want to eliminate (write out).
CREATE FUNCTION [largest_date](@var1 AS DATETIME, @var2 AS DATETIME)
RETURNS DATETIME

BEGIN
    IF @var1 IS NULL OR @var2 IS NULL
        RETURN NULL
    IF @var1 > @var2
        RETURN @var1
    RETURN @var2
END
GO

--This is my rewrite that throws the implicit conversion error

DECLARE @REG datetime2
SELECT @REG = CONVERT(datetime2, dbo.to_numb(CAST( /*--largest_date BEGIN REWRITE*/
    
CASE 
--IF @var1 IS NULL OR @var2 IS NULL RETURN NULL
WHEN isnull(USERS.MRATT, CONVERT(datetime2, '20000101', 112)) IS NULL 
        OR ISNULL(USERS.NETXY, CONVERT(datetime, '20000101', 112)) IS NULL 
THEN NULL
--IF @var1 > @var2 RETURN @var1
WHEN isnull(USERS.MRATT, CONVERT(datetime2, '20000101', 112)) 
        > ISNULL(USERS.NETXY, CONVERT(datetime, '20000101', 112))  

THEN isnull(USERS.MRATT, CONVERT(datetime2, '20000101', 112))   

--ELSE RETURN @var2
ELSE ISNULL(USERS.NETXY, CONVERT(datetime2, '20000101', 112))
END
             --END OF REWRITE
      AS float(53)), 'YYYYMMDD'), 112)
        FROM USERS

Phil Parkin

SSC Guru

Points: 247137

March 19, 2025 at 4:30 pm

Answer

#4563073

DECLARE @Var1 DATETIME = NULL
       ,@Var2 DATETIME;

SELECT Result = CASE
                    WHEN COALESCE (@Var1, @Var2) IS NULL THEN
                        NULL
                    WHEN @Var1 > ISNULL (@Var2, '19000101') THEN
                        @Var1
                    ELSE
                        @Var2
                END;

Viewing 5 posts - 1 through 4 (of 4 total)

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

stevec883 Mr or Mrs. 500 Points: 500 More actions · Answer 1

Thanks Phil, I will give that a shot. Just curious, why did you set @var1 to NULL?

ScottPletcher SSC Guru Points: 101187 More actions · Answer 2

SELECT TOP (1) reg
FROM ( VALUES(@var1), (@var2) ) AS datetimes(reg)
ORDER BY reg DESC

This approach will be really useful if you ever have to add a 3rd (4th, ...) value.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 247137 More actions · Answer 3

stevec883 wrote:

Thanks Phil, I will give that a shot. Just curious, why did you set @var1 to NULL?

That was an artifact of me testing different combinations of values and nulls. @var1 and 2 will be replaced by the actual date columns you are checking if you implement this yourself.