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

Question

Post reply

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

jagadeesh.pitchaiah

Valued Member

Points: 51
More actions
November 26, 2008 at 3:09 am

#203494

select 21/(datediff(dd,getdate(),getdate())
Hi How I can get an out put of 21............

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

A_A_R_T SSC Eights! Points: 900 More actions · Answer 1

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)

jagadeesh.pitchaiah Valued Member Points: 51 More actions · Answer 2

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.

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

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

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

A_A_R_T SSC Eights! Points: 900 More actions · Answer 4

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?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

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

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

jagadeesh.pitchaiah Valued Member Points: 51 More actions · Answer 6

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

select 21/ case when datediff(dd,getdate(),getdate()) <1 then 1 else datediff(dd,getdate(),getdate()) end

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 7

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!!!! :hehe:

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

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

A_A_R_T SSC Eights! Points: 900 More actions · Answer 8

datediff(dd,getdate(),getdate()) IS ALWAYS 0 and therefore LESS THEN 1

What is the purpose of doing that?