Saving an SQL column conversion through entire query

Question

Situation

I am trying to construct a query, using a column name: TxnDate (TransactionDate) which is of type INT. Typical dates look something like this:YYYYMMDD, so 20180209 or 20171025 as examples. I am unablt to change this fact, otherwise many things will break. It is stuck in this stupid format, unfortunately.

However, it is only useful to me as the data type DATE, so I must first convert it in a roundabout way: CONVERT(DATE, CONVERT(char(8), TransactionDate))

And to further complicate things, some of the dates are null, negative, or zero, for various reasons. So in order to get it in a usable form, I need to check to make sure that it can be converted:

CASE WHEN ISDATE(TxnDate)=1 THEN CONVERT(DATE, CONVERT(CHAR(8),TxnDate)) END

The records with bad date data are of no use to me.

The problem is that I need do check and then convert this in many different places in the WHERE statement. This has ballooned my query to slightly ridiculous proportions. I would appreciate some wisdom on a cleaner and more succinct manner of proceeding.

What I have Considered

I am reluctant to add things to the schema, but will do so if it is the better option:

1) Creating a view, which casts for me, and which I can query from, in place of the table, as demonstrated here: Is it possible to change the datatype of a column in a view? I am not partial to this, as there may be problems when attempting to cast, which invalidates the whole purpose.

2) Creating a user defined function which just runs the CASE expression when called.

3) I have tried many different variations of aliases for variables, and using the AS expression in many different ways to try and define it in one place only, but to no avail. It might be possible, but I have not been able to find it by either my own efforts or by internet search.

Again, I would appreciate any wisdom on this matter, I am new to SQL, so if there is an obvious solution, or a duplicated question, please point it out. Further, if you feel something is wrong about this question, I would prefer if you were to tell me what was incorrect, before down-voting so I get a chance to improve and learn.

Thank you

Changing the schema would be better IMHO. If you know C#, then you could write a function that would do this check and conversion for you. It would be easier to write then writing in T-SQL and that function would be part of your database (look at SQL server CLR support). — Cetin Basoz, Mar 28 '18 at 22:47
Yes, change the schema. Keeping data in the wrong data type is just asking for complications and problems. newer versions of SQL have TRY_CAST function you can use to test if the data can be converted to something else, but that is NOT the way to go. It is just a lot of work, indexing will not work, you will have to implement the same workaround everywhere. It is just lose lose — under, Mar 29 '18 at 01:25
I hear what you guys are saying, but I don't have an option when changing the datatype. There is a LOT of legacy code which relies on it being an int, which is certainly going to break if I change it. I know, whoever did it that way was a buffoon, and I am cursing them every five minutes at the moment — user7396627, Mar 30 '18 at 19:11

score 0 · Answer 1 · edited Mar 29 '18 at 03:01

You could use the query given at the end of this test. I had to generate the dummy data matching your specifications to perform the test, but all you need is the SQL at the end that produces the date column (Transaction_Date) you need.

create table #test1 (
    TxnDt int
)
go

insert #test1 values(20180328)  -- March 28, 2018
insert #test1 values(20170220)  -- Feb 20, 2017
insert #test1 values(20161201)  -- Dec 1, 2016
insert #test1 values(2016)      -- bad data 
insert #test1 values(1)         -- data
go 

select distinct * from #test1
go 

TxnDt
1
2016
20161201
20170220
20180328

-- Assuming the correct data will alays be in the form yyyymmdd, you can run the following SQL to convert it to a date type

select *
from 
(
    select distinct TxnDt 
        , case when len(cast(TxnDt as char(8))) = 8 
            then convert(date,substring(cast(TxnDt as char(8)),5,2) + '/' + substring(cast(TxnDt as char(8)),7,2) + '/' + substring(cast(TxnDt as char(8)),1,4))    
        end as Transaction_Date
    --  , case when len(substring(cast(TxnDt as char(8)),5,2) + '/' + substring(cast(TxnDt as char(8)),7,2) + '/' + substring(cast(TxnDt as char(8)),1,4)) = 10 then TxnDt end
    --  , convert(date,substring(cast(TxnDt as char(8)),5,2) + '/' + substring(cast(TxnDt as char(8)),7,2) + '/' + substring(cast(TxnDt as char(8)),1,4))   TransDate
--into #test2
    from #test1
) main
where main.Transaction_Date is not null
go

TxnDt   Transaction_Date
20161201    2016-12-01
20170220    2017-02-20
20180328    2018-03-28

select * from ( select distinct TxnDt , case when len(cast(TxnDt as char(8))) = 8 then convert(date,substring(cast(TxnDt as char(8)),5,2) + '/' + substring(cast(TxnDt as char(8)),7,2) + '/' + substring(cast(TxnDt as char(8)),1,4)) end as Transaction_Date from #test1 ) main where main.Transaction_Date is not null go — Munshi Verma, Mar 29 '18 at 00:01
Date math Transaction_Date, to substantiate our solution: select main.TxnDt , main.Transaction_Date , dateadd(mm,2,main.Transaction_Date) AS DateMathPerformed from ( select distinct TxnDt , case when len(cast(TxnDt as char(8))) = 8 then convert(date,substring(cast(TxnDt as char(8)),5,2) + '/' + substring(cast(TxnDt as char(8)),7,2) + '/' + substring(cast(TxnDt as char(8)),1,4)) end as Transaction_Date from #test1 ) main where main.Transaction_Date is not null go TxnDt Transaction_Date DateMathPerformed 20161201 2016-12-01 2017-02-01 : — Munshi Verma, Mar 29 '18 at 00:07

Saving an SQL column conversion through entire query

1 Answers1