Home Forums SQL Server 2008 SQL Server Newbies Help on Calculation of days between Start & End Date (Including BOTH the Start & End Dates) RE: Help on Calculation of days between Start & End Date (Including BOTH the Start & End Dates)

  • February 14, 2014 at 9:20 am

    #1689121

    dhananjay.nagarkar (2/14/2014)

    Dear Journey Man,

    I have to calculate the value for a field "[ORC Position Duration (months)]" using the following 2 fields from DataBase source- 1)Target Start Date , and 2) Temporary Assignment End Date.

    The 1st field Target Start Date in the table source is of the format smalldatetime example-2013-05-27 00:00:00.000, while the other field "Temporary Assignment End Date" is of the format nvarchar(255) and has values for example: "5-30-2013" - so I using these 2 fields I have to calculate the value for a field - "[ORC Position Duration (months)]" , and I need to make sure that I include both the Start Date and the End Date, and the calculation must result in a WHOLE number.

    Hope this explains more better for you to help me.

    Thanks

    DJ

    SQL Server can't compare smalldatetime with nvarchar values. For datediff, it will convert your nvarchar value into a small datetime to be able to compare both values.

    With the dates and the formula you're posting this would be the result:

    SELECT DATEDIFF( DAY, '20130527', '20130530') + 1

    --Result 4

    Is this correct?

    Note: Our names are Sean and Luis 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2