CODEWITHSUNDEEP
sqlsql-servert-sqlsql-function
redOctober13
redOctober13

Reputation: 4004

Save a column conversion for reuse in a SELECT

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

Upvotes: 0

Views: 296

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74365

You can

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.

Upvotes: 1

Dale K
Dale K

Reputation: 27451

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);

Upvotes: 3

Related Questions