Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select 21/(datediff(dd,getdate(),getdate()) Expand / Collapse
Author
Message
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
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: Thursday, July 24, 2014 8:59 AM
Points: 280, Visits: 374
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
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
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
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: Thursday, July 24, 2014 8:59 AM
Points: 280, Visits: 374
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
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
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
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
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: Thursday, July 24, 2014 8:59 AM
Points: 280, Visits: 374
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