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

manish verma 516 Reputation points
2024-01-15T16:56:39.4266667+00:00

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

Azure SQL Database
SQL Server | Other
{count} votes

Accepted answer
  1. Jingyang Li 5,896 Reputation points Volunteer Moderator
    2024-01-15T20:00:58.0266667+00:00
    select 
    datediff(day,try_convert(datetime,StartDate,103) , 
    try_convert(datetime,CurrentDate,103) )
    from test
    
    
    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Viorel 122.6K Reputation points
    2024-01-15T17:31:33.91+00:00

    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.

    1 person found this answer helpful.

  2. Olaf Helper 47,516 Reputation points
    2024-01-15T21:03:09.2333333+00:00

    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.

    0 comments No comments

  3. LiHongMSFT-4306 31,571 Reputation points
    2024-01-16T01:36:52.2066667+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.