SQL query to convert integer into date and find difference in number of months

I have 2 numbers(Integers) - 20210501 & 20210101 where the first 4 digits represent Year, next 2 digits represent Months and the last 2 digits represent Day. I want to write a SQL query to convert these integers to date and then find the difference in months between the 2 dates

2 answers

  • answered 2021-07-27 18:04 Rahul Biswas

    At first convert varchar to date format and get month diff

    -- SQL SERVER

    SELECT DATEDIFF(MONTH, CONVERT(varchar, CAST('20210101' AS DATE), 23), CONVERT(varchar, CAST('20210501' AS DATE), 23)) month_diff
    

    Alternate way

    -- SQL SERVER

    SELECT DATEDIFF(MONTH, CAST(CONVERT(varchar, CAST('20210101' AS DATE), 23) AS date), CAST(CONVERT(varchar, CAST('20210501' AS DATE), 23) AS date)) month_diff
    

  • answered 2021-07-27 18:10 Gordon Linoff

    Your two integers are conveniently in the right format for dates. So, you can convert to a string, then to a date and use datediff():

    select datediff(month,
                    convert(date, convert(varchar(255), @val1)),
                    convert(date, convert(varchar(255), @val2))
                   )
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum