Nvarchar time calculation

Question

1.50/5 (2 votes)

See more:

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.

Posted 19-Sep-14 10:37am

Member 8211588

Add a Solution

Comments

[no name] 19-Sep-14 16:43pm

http://www.sqlines.com/sql-server/functions/datediff

3 solutions

Solution 3

Never store time data in string attributes. Use appropriate date/time SQL data types, just as DATE.

—SA

Posted 19-Sep-14 11:53am

Sergey Alexandrovich Kryukov

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Baliram Suryawanshi · Accepted Answer · 2014-09-19T10:49:00

Solution 1

SQL

-- With Specified time

SELECT DATEDIFF(mi, '12:34:34', '12:34')/60;


-- with Specified Column Names
SELECT DATEDIFF(mi, OTIM, ReportedTime)/60;

Thanks,
Baliram Suryawanshi

Posted 19-Sep-14 10:49am

Baliram Suryawanshi

Updated 19-Sep-14 10:50am

v2

Comments

Member 8211588 19-Sep-14 17:07pm

I get the following error:

Conversion failed when converting date and/or time from character string.

Baliram Suryawanshi 20-Sep-14 0:58am

Execute below statement with hard coded time:
-- With Specified time

SELECT DATEDIFF(mi, '12:34:34', '12:34')/60;

It should not give any error. It works with SQL server 2012 as well.

If you are getting error in the scripts when you execute it with column names then it must be due to invalid data available in your column. With invalid I mean non-datetime data.
If it is case, first try to convert the invalid data in Time.

Abdul Samad KP · Accepted Answer · 2014-09-19T10:54:00

Solution 2

try

SELECT DATEDIFF(HOUR, CAST(OTIM AS DATETIME),CAST(ReportedTime AS DATETIME)) FROM YourTableName

If you are using SQL server 2005/2008 then this will work, I did not test in other versions

Posted 19-Sep-14 10:54am

Abdul Samad KP

Comments

Member 8211588 19-Sep-14 17:05pm

Hi I am using sql 2012 and when I tried your code, I got the following error:
Conversion failed when converting date and/or time from character string.

Abdul Samad KP 19-Sep-14 17:18pm

Sorry I don’t have a sql2012 installed on my machine to test, but I think it will work if the time format is hh:mm:ss or hh:mm. Please check all rows are having values in this format.