Iron Speed Technical Forums
Register Latest Topics
 
 
 


Reply
  Author   Comment  
krishna

Registered:
Posts: 134
Reply with quote  #1 
Hi

I calculated like this in the formula for a field (this is a number field in database).

FORMAT(PARSEDATE(myrecordcontrol.date2)-PARSEDATE(myrecordcontrol.date1),"d")
- returns a date with time.

FORMAT(PARSEDATE(myrecordcontrol.date2)-PARSEDATE(myrecordcontrol.date1),"D")
- same result.

FORMAT(PARSEDATE(myrecordcontrol.date2)-PARSEDATE(myrecordcontrol.date1),"###")
- just display ### instead of actual number.

How to get the number of days without time ?

regards
Krishna

pinbot

Avatar / Picture

MVP Consultant
Registered:
Posts: 952
Reply with quote  #2 

If the field in the database is just the difference between the two dates, I'd just make it a calculated field.

datefiff(day,date2,date1)



__________________
C. Bryan Patrick II
Iron Speed Designer MVP Consultant
Pseudo Consulting
krishna

Registered:
Posts: 134
Reply with quote  #3 
Dear Bryan

The field is not in the db.

I have a 'number' field in db that stores the days between two dates.

I simply dont know how to use the FORMAT function in the formulas tab.

Also the ISD help is confusing.. for ex.  see the help text...


formatString

A string which specifies how to format the value.  The string can be specified as a date formatter (e.g., “d”, “D”,  “f”, “F”), numeric format string (e.g., “c”, “d”, “e”, “f”, “g”), as a string with an optional currency symbol and separators (“1,234.56”, “$1,234.56”), as the value of a variable (e.g, UnitPrice), or as a Date.


I want a numeric output. But help says date format and numeric format using the same "d"... !!

Which one to use and how. Any help will be appreciated.

thanks and regards
Krishna



smithm

Registered:
Posts: 35
Reply with quote  #4 
What Bryan means it that you should calculate the difference between the two dates in the view of your table, and just display that integer result. If you aren't using views in your db (and application) currently, I strongly recommend you switch to views before you proceed. This type of scenario is one of the reasons why views are so powerful.
Gerard

Registered:
Posts: 588
Reply with quote  #5 
This scenario doesn't work when you try to calculate in real time to work out say a price for services
stopdate- startdate = days in ISD even if field is date only this calculation presents as no of days but includes a time component unlike sql datediff  basically the time component needs to be stripped of the value which may be held in a literal or some other virtual value
Basically he needs to cast the value to a integer or decimal t.o get a clean number
I have put in a request for a date diff in to formulas to handle this in a standardised way.

CAST

Use the CAST function to cast a value to an integer, string or decimal.  While casting works for most cases, there are times when there are additional formatting characters in the value. In this case, casting is not sufficient, but instead the value must be parsed to remove the symbol and separator characters. 

In these cases, use the PARSEDECIMAL, PARSEINTEGER and PARSEDATE functions.

Syntax

= CAST(value, type)

Parameters

value

An object which is to be cast to a value.  The value should be specified as an object

type

The type to which the object is to be casted. It can take any .NET basic type such int, decimal, string, etc.

Return Type

Depends on the value supplied to the type parameter.

Examples

Example

Returns

= CAST(-1.5, int)

-1

= CAST(“test”, string)

“test”

= CAST("abcd", int)

ERROR: CastElement: Cannot convert type 'String' to 'Int32'

 

JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,948
Reply with quote  #6 
For Timespan result (8.00:00:00), I don't think there's anything in-store in Formula feature that can convert it to integer. 

I believe, the runtime solution can only be done by writing few lines of code at PreRender event: datediff (VB.net) or Timespan (C#).

Cheers,
Jimi J

__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

krishna

Registered:
Posts: 134
Reply with quote  #7 
Dear Jimij

Exactly clarified my question.

There is no formula to convert timespan to number..
And yes it has to be done at runtime.. pre-render.. thanks jimij.

thx & regards
Krishna

JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,948
Reply with quote  #8 
http://www.codeproject.com/Questions/147596/TimeSpan-to-integer
__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

JBEMETO

Registered:
Posts: 160
Reply with quote  #9 
Hello Jimi,

This is the code I suppose but where do I place it. Does it need adjustments?

public static class ExtensionMethods
{
//-------------------------------------------------------------
public static decimal ToDecimal(this TimeSpan span)
{
decimal spanSecs = (span.Hours * 3600) + (span.Minutes * 60) + span.Seconds;
decimal spanPart = spanSecs / 86400M;
decimal result = span.Days + spanPart;
return result;
}
//-------------------------------------------------------------
public static TimeSpan ToTimeSpan(this decimal value)
{
int days = Convert.ToInt32(Math.Ceiling(value));
value -= days;
int time = Convert.ToInt32(value * 86400M);
TimeSpan result = new TimeSpan(1, 0, 0, time, 0);
return result;
}
}

Cheers!!!


__________________
JBEMETO
JimiJ

Avatar / Picture

MVP Developer
Registered:
Posts: 1,948
Reply with quote  #10 
Hi Benson,

It really depends on where you want to apply it and when. On my case, I use to call them in either Changed & Saving routine.


Cheers,
Jimi J

__________________

  Jaime Jegonia
[jts_logo]

Iron Speed MVP Developer
 

". . . and whoever sows generously will also reap generously" 2 Cor 9:6

Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Download Iron Speed Designer

Terms of Service Privacy Statement