Convert varchar into datetime and use date diff on the where clause

Question

I have a field ABCD stored as varchar(16) and the data stored as 20170509074744CD.

I am not versed with SQL Language. I will greatly appreciate any help converting the field ABCD into datetime and use diff function in the where clause. For example, find out last 30 days of data by filtering column ABCD:

ABCD >= DATEADD(month, -1, GETDATE()))

* Note I was able to convert the varchar into datetime with the below code but cant figure it out how to use it in the where clause and use DateDiff fuction.

CONVERT(datetime,
        (
         LEFT(ABCD,4) + SUBSTRING(ABCD,5,2) + SUBSTRING(ABCD,7,2) 
         + ' ' 
         + SUBSTRING(ABCD,9,2) + ':'+ SUBSTRING(ABCD,11,2) + ':'+ SUBSTRING(ABCD,13,2)), 
         101) AS Datetime

The 'CD' can be discarded. All we need is to use the field ABCD in the where clause along with datediff function to find last 30 days data.

Answer 1

If you can get away with just using the date for your diff, that's going to be a lot easier. You can readily convert YYYYMMDD to a date value using convert(date, '20231109', 112).

If you need the time precision, you're going to have to do some string manipulation like you have. To use it in a where clause, you just say where {your_converting_expression_here} > dateadd(month, -1, getdate())

select *
from yourTable
where CONVERT(datetime,
        (
         LEFT(ABCD,4) + SUBSTRING(ABCD,5,2) + SUBSTRING(ABCD,7,2) 
         + ' ' 
         + SUBSTRING(ABCD,9,2) + ':'+ SUBSTRING(ABCD,11,2) + ':'+ SUBSTRING(ABCD,13,2)), 
         101) > dateadd(month, -1, getdate()

If you don't want all that gobbldygook in your query, you can abstract the logic into a function like this:

create or alter function dbo.ParseUglyString
(
    @UglyString varchar(16)
)
returns table
as
return
with a as
(
    select 
        Yr = substring(@UglyString, 1, 4),
        Mon = substring(@UglyString, 5, 2),
        D = substring(@UglyString, 7, 2),
        H = substring(@UglyString, 9, 2),
        M = substring(@UglyString, 11, 2),
        S = substring(@UglyString, 13, 2)
)
select 
    a.Yr,
    a.Mon,
    a.D,
    a.H,
    a.M,
    a.S,
    b.ReconstructedDate
from a
cross apply
(
    select ReconstructedDate = datetimefromparts(Yr, Mon, D, H, M, S, 0)
) b

To call it, do:

drop table if exists #SomeData
select 
    Dt,
    DtStr,
    StringVal = MDY + H + M + S + 'CD'
into #SomeData
from sys.all_objects a
cross apply (select Dt = dateadd(day, checksum(newid()) % 60, getdate())) b
cross apply (select DtStr = convert(varchar(30), Dt, 120)) c
cross apply
(
    select 
        MDY = convert(char(8), Dt, 112),
        H = substring(DtStr, 12, 2),
        M = substring(DtStr, 15, 2),
        S = substring(DtStr, 18, 2)
) d

select *
from #SomeData a
cross apply dbo.ParseUglyString(a.StringVal) b
where b.ReconstructedDate > dateadd(month, -1, getdate())

Answer 2

Performance aside and design choices aside, here is your code -

CREATE TABLE #table
(
    id INT
  , ABCD VARCHAR(64)
);

INSERT INTO #table
VALUES
(1, '20170509074744CD')
, (2, '20231109073322EF');

SELECT id
FROM #table
WHERE CAST(LEFT(ABCD, 8) AS DATE) >= DATEADD(mm, -1, GETDATE());

Or if you want to include time in your evaluation -

SELECT id
FROM #table
WHERE CAST(STUFF(STUFF(STUFF(LEFT(ABCD, 14), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS DATETIME) >= DATEADD(
                                                                                                             mm
                                                                                                           , -1
                                                                                                           , GETDATE()
                                                                                                         );

If this is a frequent operation, you should add a datetime column to the table, populate it with the parsed datetime value from column ABCD, and update your business logic to also insert that value on record creation. It really wont scale well as long as you are using the wrong datatype for the job.

But if there are a few records and you don't care, to make the query more simple, you could add a computed column on the table and use that for your query.

For date -

ALTER TABLE #table
ADD record_datestamp AS (CAST(LEFT(ABCD, 8) AS DATE));

SELECT id
     , ABCD
     , record_datestamp
FROM #table

SELECT id
FROM #table
WHERE record_datestamp > DATEADD(mm, -1, GETDATE())

For datetime -

ALTER TABLE #table
ADD record_timestamp AS (CAST(STUFF(STUFF(STUFF(LEFT(ABCD, 14), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS DATETIME));

SELECT id
     , ABCD
     , record_timestamp
FROM #table;

SELECT id
FROM #table
WHERE record_timestamp > DATEADD(mm, -1, GETDATE());

Neither can be a key column in an index because neither are deterministic, but at least the code can be simplified.