Get difference between two dates in absolute format.

Question

0.00/5 (No votes)

See more:

Hello There,
I tried to run this query but it didn't work for me.

C#

@StartDt NVARCHAR(25),
@Period INT 

UPDATE CROS_L SET DUE5 = bal_amt WHERE ABS(CONVERT(DATETIME,@StartDt,103)- due_date) >= @Period AND due_date IS NOT NULL;

Exception:

SQL

Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.

Need Suggestion
Best Regards

Posted 17-Mar-13 19:26pm

Mayank Topiwala

Updated 17-Mar-13 19:44pm

v3

Add a Solution

Comments

S R JHALA 18-Mar-13 1:55am

what is Period here and why you check >= @Period
please write what you exactly looking for

Mayank Topiwala 18-Mar-13 2:00am

Period is any integer value say 30
so the query update the table if difference between two dates is greater than or equal to 30.

2 solutions

Solution 2

SQL

DECLARE 
 @StartDt DATETIME = '2013-03-11 11:22:52.053'
,@Period INT = 1 
,@due_date DATETIME = '2013-03-18 11:22:52.053'


--This will Update as it will return 7 as DATEDIFF which is greater than period which is 1 over here
UPDATE CROS_L SET DUE5 = bal_amt WHERE DATEDIFF(D,@StartDt,@due_date) >= @Period AND @due_date IS NOT NULL;



SET @Period = 10 

--This will not Update as it will return 7 as DATEDIFF which is not greater than period which is 10 over here
UPDATE CROS_L SET DUE5 = bal_amt WHERE DATEDIFF(D,@StartDt,@due_date) >= @Period AND @due_date IS NOT NULL;

Posted 17-Mar-13 20:08pm

S R JHALA

Comments

Mayank Topiwala 18-Mar-13 2:13am

Thank You!

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)

Aarti Meswania · Accepted Answer · 2013-03-17T19:55:00

Solution 1

SQL

@StartDt NVARCHAR(25),
@Period INT

UPDATE CROS_L SET DUE5 = bal_amt WHERE ABS(datediff(day,CONVERT(DATETIME,@StartDt,103), due_date)) >= @Period AND due_date IS NOT NULL;

Happy Coding!
:)

Posted 17-Mar-13 19:55pm

Aarti Meswania

Updated 17-Mar-13 20:04pm

v2

Comments

Mayank Topiwala 18-Mar-13 1:58am

It shows incorrect syntax near '>'.

Aarti Meswania 18-Mar-13 2:02am

see updated solution

Mayank Topiwala 18-Mar-13 2:13am

Thank You!

Aarti Meswania 18-Mar-13 2:15am

most welcome!
Glad to help u! :)