Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 Compact Edition » select 21/(datediff(dd,getdate(),getdate())
Add to briefcase

select 21/(datediff(dd,getdate(),getdate()) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
jagadeesh.pitchaiah
Posted Wednesday, November 26, 2008 3:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2008 7:04 AM
Points: 3, Visits: 4
select 21/(datediff(dd,getdate(),getdate())

Hi How I can get an out put of 21............
Post #608918
A_A_R_T
Posted Wednesday, November 26, 2008 3:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 1:48 AM
Points: 276, Visits: 327
What do you mean? Output of 21?

In your code there's a ")" missing. Like this:

select 21/(datediff(dd,getdate(),getdate()))

But it's no good anyway.

This code will give you 21 as output but I'm quite sure this is not what you mean:

select (datediff(dd,getdate(),getdate()) + 21)
Post #608925
jagadeesh.pitchaiah
Posted Wednesday, November 26, 2008 3:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2008 7:04 AM
Points: 3, Visits: 4
Hi Thanks for your reply..

my requirement is,

select 21/datediff(dd,getdate(),getdate())
if I execuite above i will get an error as Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

but I want top value( ie A/datediff(dd,getdate(),getdate()), I want ans as A)

the date diff is 0, but we have to make it as 1, so that I will get top value as 21.
Post #608933
Christopher Stobbs
Posted Wednesday, November 26, 2008 3:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Is this a real life example.

Cause if you always doing a date diff with GETDATE vs GETDATE then why don't you just do this.

SELECT 21


I think something is missing here.

Do you have sample data with other examples

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #608938
A_A_R_T
Posted Wednesday, November 26, 2008 3:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 1:48 AM
Points: 276, Visits: 327
Hmm...I still don't understand what you want?

This code is what I believe you're asking for:

select 21 / (datediff(dd,getdate(),getdate()) + 1)

But you could just as easily replace (datediff(dd,getdate(),getdate()) + 1) by just 1.

select 21 / 1

Above code is the same.

Why do you want to subtract today from today ( GETDATE() - GETDATE() )? What's the purpose of doing that?
Post #608940
Christopher Stobbs
Posted Wednesday, November 26, 2008 3:56 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Or is the situation something like this

DECLARE @TABLE TABLE
(Number INT,
Date1 DATETIME,
Date2 DATETIME)

INSERT INTO @TABLE
SELECT 21,GETDATE(),GETDATE() UNION ALL
SELECT 21,GETDATE(),DATEADD(dd,2,GETDATE()) 

SELECT  Number/CASE WHEN (datediff(dd,Date1,Date2)) = 0 THEN 1 ELSE (datediff(dd,Date1,Date2)) END
FROM @TABLE



Which case I used a case statement to check for 0 before dividing
you could right it differently but a case statement would work for you.

Thanks
Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #608942
jagadeesh.pitchaiah
Posted Wednesday, November 26, 2008 3:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 26, 2008 7:04 AM
Points: 3, Visits: 4

HI Just see below...............


select 21/ case when datediff(dd,getdate(),getdate()) <1 then 1 else datediff(dd,getdate(),getdate()) end
Post #608943
Christopher Stobbs
Posted Wednesday, November 26, 2008 4:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, February 24, 2010 4:10 AM
Points: 1,553, Visits: 2,232
Is that the solution you using or the problem you having?

We still don't understand why you feel the need to test GETDATE vs GETDATE cause the answer is ALWAYS the same!!!!

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley


Posting Best Practices
Numbers / Tally Tables

SQL-4-Life
Post #608947
A_A_R_T
Posted Wednesday, November 26, 2008 4:07 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 1:48 AM
Points: 276, Visits: 327
datediff(dd,getdate(),getdate()) IS ALWAYS 0 and therefore LESS THEN 1

What is the purpose of doing that?
Post #608951
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.