Comparing Dates ???

I have tow date objects.

STARTDATE - Object in Database

CURRDATE - UserDefinedObject

Both these values give me the date in this format:
mm/dd/yy hh/mm/ss; though I set the Object format to dd/mm/yy.

So what happens is I’m not able to equate these objects as they do not match up at the time level, even if the dates are same. I’m only concerend with the dates.

For Example, if STARTDATE = 06/25/04 11:59:34 am; and
CURRDATE = 06/25/04 10:33:46 am, then the query doesnt return any value. But I want the start date to be returned.


tendulkar :india: (BOB member since 2004-03-15)

Your best bet:
Ask your universe designer to provide a Current Date universe object with no or zero timestamp (truncated) and ask him/her to provide a Start Date object without a timestamp (truncated).


Andreas :de: (BOB member since 2002-06-20)

Define StartDt object = Trunc(STARTDATE) and CurrDate = Trunc(Sysdate) in the Universe.


JaiGupta (BOB member since 2002-09-12)

When you change the object format to dd/mm/yy, I presume your doing this in the report, it is done after the 2 dates are compared. The format of the 2 objects your comparing needs to change (whether to mm/dd/yy or dd/mm/yy) before the sql statement that is comparing them. Therefore, it needs to change at the Universe level. Another suggestion, other than trunc(), is to change the objects at the universe level by using another function, to_char(,‘dd/mm/yy’), so the object will already be formatted the way you want it in the report without formatting it in the report. (keep in mind it is changing the datatype of the object to character). IF you want the datatype to stay as date, then try to_date(to_char(,‘dd/mm/yy’)).

Tony


aantista :us: (BOB member since 2002-11-05)

And as a rule of thumb you want it to stay of data type DATE, because:

  • you can continue using data arithmetics
  • will sort chronologically correct

Andreas :de: (BOB member since 2002-06-20)

I’m using SQL Server…any clues how to accomplish it in SQL Server.


tendulkar :india: (BOB member since 2004-03-15)

Please, go to www.microsoft.com and search for BOL (Books Online) for SQL server. All the SQL Server documentation is available online.

Or at least try a search on BOB.


Andreas :de: (BOB member since 2002-06-20)

Use CONVERT ( Date,StartDate,‘mm/dd/yyyy’ ).


JaiGupta (BOB member since 2002-09-12)