Sample Header Ad - 728x90

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

0 votes
2 answers
523 views
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.
Asked by SQL_NoExpert (1117 rep)
Nov 9, 2023, 12:21 PM
Last activity: Nov 9, 2023, 07:06 PM