How to subtract two date of format DD/MM/YYYY HH:MM:SS in SQL Server

Question

How to subtract two date of format DD/MM/YYYY HH:MM:SS in SQL Server

manish verma 516

Hi All, i have two dates type of nvarchar in SQL Table shown in below StartDate CurrentDate 02/01/2024 16:25:27 15/01/2024 15:36:36 when we apply datdiff(day,StartDate,CurrentDate) in Azure SQL it is error out please help

SSingh-MSFT 16,371 Reputation points Moderator

2024-01-17T09:54:34.0233333+00:00

Hi manish verma •, Just checking in to see if the provided suggestion helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know. Thanks

Accepted answer

3 additional answers

Your answer

SSingh-MSFT 16,371 Reputation points Moderator

2024-01-17T09:54:34.0233333+00:00

Hi manish verma •, Just checking in to see if the provided suggestion helped. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know. Thanks

Answer 1

Jingyang Li 5,896 Volunteer Moderator

select 
datediff(day,try_convert(datetime,StartDate,103) , 
try_convert(datetime,CurrentDate,103) )
from test

manish verma 516 Reputation points

2024-01-18T14:30:11.0933333+00:00

This will work

Answer 2

Viorel 122.7K

Try to execute set dateformat dmy before your query. Or use this: datediff(day, convert(date, StartDate, 103), convert(date, CurrentDate, 103)). It is probably convenient to use the datetime datatype instead of nvarchar.

manish verma 516

set dateformat dmy is only change format at SP session or all other query running at the same time
get impacted please clear this

manish verma 516 Reputation points

2024-01-15T18:26:58.8533333+00:00
Sorry, I cannot use , it will impact other calculation

set dateformat dmy
manish verma 516 Reputation points

2024-01-15T18:27:16.73+00:00
Sorry, I cannot use , it will impact other calculation

set dateformat dmy
Viorel 122.7K Reputation points

2024-01-15T21:38:19.95+00:00

If you cannot use set dateformat, then try datediff(day, convert(date, StartDate, 103), convert(date, CurrentDate, 103)).
manish verma 516 Reputation points

2024-01-18T14:26:44.8433333+00:00
I think we set

set dateformat dmy it will work SP session

Answer 3

Olaf Helper 47,516

have two dates type of nvarchar in SQL Table

That's already the failure, never store dates as string; store them as dates.

when we apply datdiff(day,StartDate,CurrentDate) in Azure SQL it is error out

Sure, you store it as string, not as date. You have to convert the data, see previous answers.

Answer 4

Hi @manish verma

i have two dates type of nvarchar in SQL Table shown in below StartDate CurrentDate when we apply datdiff(day,StartDate,CurrentDate) in Azure SQL it is error

See this doc: DATEDIFF (Transact-SQL)

The values of StartDate and CurrentDate should be one of the following values:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

Apparently, nvarchar is not supported when using this function.

You need to convert nvarchar values to date values first, then apply datediff function.

Please refer to this doc for more details about CAST and CONVERT.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

How to subtract two date of format DD/MM/YYYY HH:MM:SS in SQL Server

3 additional answers

Your answer