CODEWITHSUNDEEP
sql-server
SuryaPrakash
SuryaPrakash

Reputation: 33

How to find difference in days from DD-MM-YYYY formatted dates in SQL Server?

I want to find the difference between two dates in DD-MM-YYYY format. For example, I have 2 dates 29-10-2018 and 29-11-2018. I want to find the difference in number of days between those two dates (30 days) in SQL Server.

Upvotes: 1

Views: 1702

Answers (3)

DatabaseCoder
DatabaseCoder

Reputation: 2032

You can change the date format of current session and then use DateDiff function.

SET DATEFORMAT 'dmy'

SELECT DATEDIFF(DAY, '29-10-2018', '29-11-2018')

I will check more about Set DateFormat before adding this to Production code.

That changes the SESSION date format, not the DATABASE.

[Note from Previous Post: This is often not the way to solve the problem of interpreting dates. Datetimes should not be stored a strings if you can avoid it (use a datetime or date column instead). If you have to store in a string form, use an ISO 8601 format which is basically of the form YYYYMMDD]

Upvotes: 1

Santhana
Santhana

Reputation: 417

declare @date1 varchar(10),@date2 varchar(10)

set @date1 = '01-11-2018'

set @date2 = '28-11-2018'

select

        datefromparts(
                      right(left(@date2 ,10),4),
                      substring(left(@date2 ,10),4,2),
                      left(left(@date2 ,10),2)
                      )

select DATEDIFF ( DAY,

                datefromparts( right(left(@date1 ,10),4),

                       substring(left(@date1 ,10),4,2),

                       left(left(@date1 ,10),2)

                     ),

        datefromparts( right(left(@date2 ,10),4),

            substring(left(@date2 ,10),4,2),

            left(left(@date2 ,10),2)

            )

    )

Upvotes: 0

Buchiman
Buchiman

Reputation: 320

Use DATEDIFF

SELECT  DATEDIFF(day, '2018-10-29 00:00:00.000', '2010-11-29 00:00:00.000')

Upvotes: 0

Related Questions