need urgent help on a calculation

• dhananjay.nagarkar

  Default port

  Points: 1496

  More actions

  August 13, 2013 at 11:47 am

  #281163

  Dear friends,

  I have to do a calcuation for field Duration using the formula as below-

  (Temporary Assignment End Date field - Target Start Date field)/30.

  and I need to Format the result of the formula as numeric, display to two decimal places.

  the database has the 2 fields in the type and format as -

  Req# Target Start Date ( DATETIME) example:2012-08-01 00:00:00.000

  Temporary Assignment End Date ( NVARCHAR9255)) example: 09-01-2014

  Need urgent hlep to solve this using SQL please since I'm new to SQL.

  kind Regards

  DJ

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 13, 2013 at 12:03 pm

  #1641064

  Apparently all of your requirements are urgent and you haven't learned much from SQL in the last 7 months you've been around here.

  Please post DDL, sample data and expected results to get some help.

  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

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  August 13, 2013 at 12:08 pm

  #1641067

  Try this...

  --== TEST DATA ==--

  SET DATEFORMAT DMY -- Assumes 09 = day, 01 = month

  DECLARE @testing AS TABLE (

  TargetStartDate DateTime,

  TemporaryAssignmentEndDate nvarchar(255)

  )

  INSERT INTO @testing VALUES ('01 Aug 2012', '09-01-2014')

  --== TRY THIS ==--

  SELECT *,

  CAST(TemporaryAssignmentEndDate as DateTime),

  Duration_Days = DATEDIFF(dd, TargetStartDate,CAST(TemporaryAssignmentEndDate as DateTime)),

  Duration = DATEDIFF(dd, TargetStartDate,CAST(TemporaryAssignmentEndDate as DateTime))/30

  FROM @testing