>
>
>How do I compare DATETIME() values in a SQL view?
>
>If I create a view like this:
>
>CREATE SQL VIEW "V_Order2" ;
>REMOTE CONNECT "WAREHOUSE" SHARED AS ;
>SELECT *;
>FROM orders Orders;
>ORDER BY Orders.timestamp ;
>where Orders.timestamp > datetime() - 86000
>
>I get an error for Incorrect syntax. What I want to do is see all
>records for today's date.
>
>I've played around with the SQL date functions, but can't seem to
>figure
>out a combination that allows me to compare these values. Seems that
>the view doesn't like VFP nor SQL functions on the right of the >.
Two things:
1) Don't forget that the syntax has to be appropriate for the back end, and
I don't think that datetime() is valid SQL Server. Perhaps:
where DATEFIDD(day, Orders.TimeStamp, GetDate()) = 0
2) I wouldn't expect this to return the orders for today, but it should
return them for the past 24 hours. To convert the last bit to today,
(in FP syntax) use something like:
where Orders.TimeStamp > DTOT(DATE())
/Paul
