Length of Stay in Days from datetime

[imtheodore]

I have an
admitdatetime----->01/01/2013 00:22:00
and
dischargedatetime ---->01/09/2013 22:56:29
I need to calculate the number of days between the two, when I use
(datediff (d, admitdatetime,dischargedatetime)
I only get the correct number of days a small number of times
0-14 hours = 1 day
24:01-48 hours = 2 days
etc...
But using the datediff function the number of days often comes up a day short

Thanks

 

[boblarson]

You can do this:

CInt(DateDiff("h",admitdatetime,dischargedatetime)/24)

 

[imtheodore]

You can do this:

CInt(DateDiff("h",admitdatetime,dischargedatetime)/24)

That formula calculates 1 for
Admit
2013-01-01 00:36:00.000
Discharge
2013-01-02 16:03:05.700
The answer should be 2 since the timeframe is between 24-48

It seems like it should be easy, but I'm stumped...

 

[boblarson]

That formula calculates 1 for
Admit
2013-01-01 00:36:00.000
Discharge
2013-01-02 16:03:05.700
The answer should be 2 since the timeframe is between 24-48

It seems like it should be easy, but I'm stumped...

Worked fine for me.

Maybe you need to use this:

CInt(DateDiff("h",Format(admitdatetime,"\#mm\/dd\/yyyy\#"),Format(dischargedatetime,"\#mm\/dd\/yyyy\#"))/24)

And yes, it is important to format it EXACTLY as shown.

 

[mdlueck]

I believe call to CInt() is dropping the precision. You really mean if it did not come out exact, if there was anything left, then you want the number rounded up.

The bare call to CInt() is applying standard rounding rules... Lower than .5 goes down / greater than goes up.

I believe I have done that (If greater than a whole number, then ALWAYS round up) somewhere in a Stored Procedure, I am just having a hard time putting my finger on it at the moment...

 

[boblarson]

I believe call to CInt() is dropping the precision. You really mean if it did not come out exact, if there was anything left, then you want the number rounded up.

The bare call to CInt() is applying standard rounding rules... Lower than .5 goes down / greater than goes up.

I believe I have done that (If greater than a whole number, then ALWAYS round up) somewhere in a Stored Procedure, I am just having a hard time putting my finger on it at the moment...

It isn't dropping it for me but it could for other times so it would probably be best to go with:

'
 
DateDiff("h",Format(admitdatetime,"\#mm\/dd\/yyyy\#"),Format(dischargedatetime,"\#mm\/dd\/yyyy\#"))\24 + IIf(DateDiff("h",Format(admitdatetime,"\#mm\/dd\/yyyy\#"),Format(dischargedatetime,"\#mm\/dd\/yyyy\#")) MOD 24 > 0, 1,0)
 
'

 

[mdlueck]

I believe I have done that (If greater than a whole number, then ALWAYS round up) somewhere in a Stored Procedure, I am just having a hard time putting my finger on it at the moment...

I found it. SQL version (snippet):

    BEGIN
      --First subtract the first 99 numeric Revision numbers
      SET @sintInput = @sintInput - 99
      SET @sintR = (@sintInput % 26) + 64
      IF (@sintR = 64)
        BEGIN
          SET @sintN = ((@sintInput / 26) + 64) - 1
          SET @sintR = 90
        END
      ELSE
        BEGIN
          SET @sintN = ((@sintInput / 26) + 64)
        END
        SET @strRC = CHAR(@sintN) + CHAR(@sintR)
    END

Same code in VBA logic:

    'First subtract the first 99 numeric Revision numbers
    intInput = intInput - 99
    intR = (intInput Mod 26) + 64
    If intR = 64 Then
      intN = ((intInput \ 26) + 64) - 1
      intR = 90
    Else
      intN = (intInput \ 26) + 64
    End If
    revconv_ConvN2A = Chr(intN) & Chr(intR)

So per this sample code, if the number perfectly / cleanly divided, then the code assumes the Remainder number and subtracts one from the "tens" position. If it comes back with a remainder indicating that it was not cleanly divided it keeps that computed remainder and then calculates the "tens" position not subtracting one from the calculation.

 

[boblarson]

I found it. SQL version (snippet):

    BEGIN
      --First subtract the first 99 numeric Revision numbers
      SET @sintInput = @sintInput - 99
      SET @sintR = (@sintInput % 26) + 64
      IF (@sintR = 64)
        BEGIN
          SET @sintN = ((@sintInput / 26) + 64) - 1
          SET @sintR = 90
        END
      ELSE
        BEGIN
          SET @sintN = ((@sintInput / 26) + 64)
        END
        SET @strRC = CHAR(@sintN) + CHAR(@sintR)
    END

Same code in VBA logic:

    'First subtract the first 99 numeric Revision numbers
    intInput = intInput - 99
    intR = (intInput Mod 26) + 64
    If intR = 64 Then
      intN = ((intInput \ 26) + 64) - 1
      intR = 90
    Else
      intN = (intInput \ 26) + 64
    End If
    revconv_ConvN2A = Chr(intN) & Chr(intR)

Mine's shorter.

(oh and for the last one I posted the thing of which to be aware is the use of the BACKWARDS slash which gives a whole number.

 

[imtheodore]

It isn't dropping it for me but it could for other times so it would probably be best to go with:

'
 
DateDiff("h",Format(admitdatetime,"\#mm\/dd\/yyyy\#"),Format(dischargedatetime,"\#mm\/dd\/yyyy\#"))\24 + IIf(DateDiff("h",Format(admitdatetime,"\#mm\/dd\/yyyy\#"),Format(dischargedatetime,"\#mm\/dd\/yyyy\#")) MOD 24 > 0, 1,0)
 
'

I cannot use the "Format" commend in SQL

 

[boblarson]

And again the filter NEW POSTS does it to me. I miss the fact that we are in the SQL Server category. So, I will say...

NEVER MIND

 

[imtheodore]

And again the filter NEW POSTS does it to me. I miss the fact that we are in the SQL Server category. So, I will say...

NEVER MIND

That's ok, thanks for trying!

I'll eventually figure it out..

 

[mdlueck]

Mine's shorter.

Mine is intended for use with Base 36 numbers!!! L O L...

 

[imtheodore]

I found it. SQL version (snippet):

    BEGIN
      --First subtract the first 99 numeric Revision numbers
      SET @sintInput = @sintInput - 99
      SET @sintR = (@sintInput % 26) + 64
      IF (@sintR = 64)
        BEGIN
          SET @sintN = ((@sintInput / 26) + 64) - 1
          SET @sintR = 90
        END
      ELSE
        BEGIN
          SET @sintN = ((@sintInput / 26) + 64)
        END
        SET @strRC = CHAR(@sintN) + CHAR(@sintR)
    END

Same code in VBA logic:

    'First subtract the first 99 numeric Revision numbers
    intInput = intInput - 99
    intR = (intInput Mod 26) + 64
    If intR = 64 Then
      intN = ((intInput \ 26) + 64) - 1
      intR = 90
    Else
      intN = (intInput \ 26) + 64
    End If
    revconv_ConvN2A = Chr(intN) & Chr(intR)

So per this sample code, if the number perfectly / cleanly divided, then the code assumes the Remainder number and subtracts one from the "tens" position. If it comes back with a remainder indicating that it was not cleanly divided it keeps that computed remainder and then calculates the "tens" position not subtracting one from the calculation.

Problem #2 for me, I have read-only access to the db, I cannot create stored procedures. It makes my life miserable. But I'll copy this for use in my other projects where I have full access.

 

[mdlueck]

I cannot create stored procedures.

The SQL is not specific to being stored on the server. You should be able to send it in as part of a Pass-Through query.

And technically that was from a UDF which SP's themselves make use of.

 

[imtheodore]

I pretty sure I figured it out, in case anyone else ever needs it...
CEILING((DATEDIFF(minute, admitdatetime, dischargedatetime) / 60.0) / 24.0)