Newbie Question on =Today()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

VB - 30 Aug 2006 13:39 GMT

I need to pull some reports from an older ERP system in the US via ODBC

I have the report in play, but want to limit the criteria to the items
shipped today

I have a TODAY_FIELD as a calculated Field with the parameter =TODAY()

The Format of the Oracle view is mm/dd/yyyy hh:mm:ss AM and the
TODAY_FIELD
matches that. (ie 8/30/2006 12:00:00 AM)

So I am looking for the criteria for WHERE (REAL_SHIP_DATE >
TODAY()) which will not generate and ORA-00904 error.

Can anyone please help?

Reply to this Message

Ben Watts - 30 Aug 2006 14:05 GMT

getdate() is the system date to use. Or am I understanding what you want
wrong?

>I need to pull some reports from an older ERP system in the US via ODBC
>
[quoted text clipped - 11 lines]
>
> Can anyone please help?

Reply to this Message

VB - 30 Aug 2006 14:15 GMT

> getdate() is the system date to use. Or am I understanding what you want
> wrong?
[quoted text clipped - 13 lines]
> >
> > Can anyone please help?

I am looking to report all shipping transactions (TRANSACTION_CODE =
'OESHIP') which have happened since Midnight.

I know = TODAY() will give you 12:00am today, how do I pass this as a
criteria to a ODBC database call?

I had tried =TODAY() but this is rejected, it seems to suggest I need
to use TO_DATE, but I don't know how to do this either.

Thanks for getting back though :)

Reply to this Message

magendo_man - 31 Aug 2006 10:03 GMT

Try:

WHERE REAL_SHIP_DATE > CAST( CONVERT( VARCHAR(8), GETDATE(), 112) AS DATETIME)

This takes the the curent system time, as suggested by Ben, chops of the
time and returns a DateTime value at the very start of the day, similar to
the vb Today() function.

HTH,
Magendo_man

> > getdate() is the system date to use. Or am I understanding what you want
> > wrong?
[quoted text clipped - 24 lines]
>
> Thanks for getting back though :)

Reply to this Message

VB - 31 Aug 2006 10:32 GMT

> Try:
>
[quoted text clipped - 35 lines]
> >
> > Thanks for getting back though :)

THanks, that would do the trick.

When I try it though I get a new error, INVALID or MISSING EXPRESSION

Any ideas?

Reply to this Message

VB - 31 Aug 2006 13:10 GMT

> > Try:
> >
[quoted text clipped - 41 lines]
>
> Any ideas?

SOrry Folks, I realised (eventually) instead of trying to re-invent the
wheel I should just create a view in the database with the sysdate -1
call and just report the view.

Reply to this Message