Save a column conversion for reuse in a SELECT-CodePudding

There's a SELECT in a stored procedure that has some nested case-when blocks, and this thing comes up about 5 times in different or blocks:

CONVERT(DATE, ADJDOH) <= CONVERT(DATE, ADJDOT)

These fields can have a DATETIME, be blank (an empty string) or NULL, or, an invalid value, which makes the CONVERT function fail. Is there any way I can convert these fields once and then be able to do the comparisons, or null checks more easily than using the CONVERT function so many times?

In the following example row 4 gives an error if the date is invalid.

CREATE TABLE repro (
    id int,
    date1 varchar(10),
    date2 varchar(10)
)

INSERT INTO repro values
(1, '12/31/2021', '12/30/2021'),
(2, NULL, '12/14/2021'),
(3, '',''),
(4, '12/30/202', '12/30/2021')

SELECT 
    CONVERT(DATE,date1) as D1,
    CONVERT(DATE,date2) as D2
from repro 
where 
--id = 1
--id = 2
--id = 3
id = 4

drop table repro

Here's a snippet of the code showing a bit more of the context (can't share the whole code):

CodePudding user response：

CROSS APPLY TRY_CONVERT are what you want here. CROSS APPLY allows you to carry out any calculations/conversions/etc in one place without having to duplicate the logic. TRY_CONVERT allows you to detect and/or recover from bad data.

SELECT 
    D.Date1 AS D1
    , D.Date2 AS D2
FROM repro 
CROSS APPLY (
  -- Perform any calculations/conversions here, once
  VALUES (TRY_CONVERT(DATE,date1), TRY_CONVERT(DATE,date2))
) AS D (Date1, Date2);

CodePudding user response：

You can

Add a _computed column(s)
Define a view on the table that provides the needed column

You'll still need to handle the bad data though, one way or another, though. But either of these gives your a persistent way of providing the date conversion without need to repeat the logic over and over again.

[Frankly, I'd clean the data in the table, and then put a check constraint on the column to ensure that bad date fails any insert or updates. Or, better, convert the column to date and have done with it — though that is likely to break existing code that expect the column to be char/varchar.