SQL query to compare date columns with int column with difference of 180 days or more-CodePudding

In a table named table there are two columns opendate(datatype-datetime) and Xdate(datatype-int). In result I need value in which the difference between opendate and Xdate should be greater than or equal to 180days.

Table:

Name	opendate	Xdate
AAA	2022-01-01 00:00:00	20210815
BBB	2022-01-02 00:00:00	20211215

Expected result :

Name	opendate	Xdate
AAA	2022-01-01 00:00:00	20210815

Since there is more than 180 days gap difference between Xdate and opendate also I am using sql server mangement studio.

CodePudding user response：

Try the below SQL script, replace the placeholder 'table' with your table name (without quotes), and syntax is for SQL Server:

SELECT * from 'table' Where DATEDIFF (d, CONVERT (datetime,convert(char(8),xdate)),opendate) >= 180

CodePudding user response：

You need to convert the integer to a CHAR before you can convert to date. DATEDIFF

SELECT *
FROM Table1
WHERE DATEDIFF(day, CONVERT (DATE, CONVERT(CHAR(8), Xdate)), opendate) >= 180;