How To Use DATEDIFF in sql server 2005

Question

1.44/5 (2 votes)

See more:

Hi All,

when i try to diff between minute in sql server 2005, it i try this code working fine

SQL

select datediff(minute, '2011-01-02 12:00:00', '2011-01-02 12:30:00') as minutes

But This code showing Error, can you help me how to slove this one

SQL

SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Thanks!

Posted 26-Aug-14 1:01am

prasanna.raj

Add a Solution

Comments

hypermellow 26-Aug-14 7:05am

... could you update the question to include your error message please?

prasanna.raj 26-Aug-14 7:10am

your are using SQL SERVER 2008 ?

prasanna.raj 26-Aug-14 7:09am

Error! : Conversion failed when converting datetime from character string.

2 solutions

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)

Pikoh · Accepted Answer · 2014-08-26T01:17:00

Solution 1

Try this:

SQL

SELECT DATEDIFF(minute, '2005-12-31T23:59:59.999', '2006-01-01T00:00:00.999');

Put a 'T' between date and time,and use just 3 digits for milliseconds

Posted 26-Aug-14 1:17am

Pikoh

Updated 26-Aug-14 1:18am

Comments

prasanna.raj 26-Aug-14 7:53am

Thanks!...

Pikoh 26-Aug-14 8:09am

You're welcome. BTW,you accepted solution 2,does that work to you?

prasanna.raj 26-Aug-14 8:13am

why we reduce the ms any reason for that

59.9999999 to 59.999

Pikoh 26-Aug-14 8:18am

That's by definition,i think it's in ISO 8601

prasanna.raj 26-Aug-14 8:23am

oh ! thanks ...

Magic Wonder · Accepted Answer · 2014-08-26T01:30:00

Solution 2

Hi,

Try this...

SQL

SELECT DATEDIFF(minute, '2005-12-31 23:59:59.999', '2006-01-01 00:00:00.000');

Hope this will help you.

Cheers.

Posted 26-Aug-14 1:30am

Magic Wonder