Nvarchar Time Calculation

Question

Post reply

Nvarchar Time Calculation

ateeq.weera

Grasshopper

Points: 17
More actions
September 19, 2014 at 2:44 pm

#299601

I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. Some one please help me. I tried substring to trim the OTIM, I am unable to make it work.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Can you be more specific about what you tried?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You shouldn't be storing times as nvarchar. SQL Server has a time datatype (and plenty others datetime types).

We also have the function DATEDIFF available so we don't have to reinvent the wheel to do time calculations. DATEDIFF can implicitly convert well formatted strings into an adequate data type. Just be sure to have the correct values or you'll find errors.

Check the following example:

DECLARE @Sample TABLE(

OTIM nvarchar(8),

ReportedTime nvarchar(5))

INSERT INTO @Sample

VALUES

( '12:34:34', '12:34'),

( '12:34:34', '13:34'),

( '12:34:34', '11:34'),

( '12:34:34', '12:00'),

( '12:34:34', '1:00')

SELECT *,

CAST(OTIM AS TIME) OTIM_As_Time,

CAST(ReportedTime AS TIME) ReportedTime_As_Time,

DATEDIFF(HH, OTIM, ReportedTime) Difference_In_Hours

FROM @Sample

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

The problem with DATEDIFF is that it's sensitive to temporal borders. For example, the following two times are only 3 milliseconds apart but the DATEDIFF for hours between the two returns a "1".

SELECT DATEDIFF(hh,'12:59:59.997', '13:00');

An easy correction for this "feature" is to simply convert the times to DATETIME, subtract the smaller from the larger, and then do a DATEDIFF between 0 and that previously calculated difference to get the number of whole hours that have passed (without rounding).

SELECT DATEDIFF(hh,0,CAST('13:00' AS DATETIME)-CAST('12:59:59.997' AS DATETIME))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)