I had thought of this, it just is awfully verbose
I've quite a lot of these date comparison queries to convert, I wonder
if there's another way which avoids the complication of the H:M:S at
all?
One way, I suppose, would be to have a new longint column populated by a
trigger on the date column, using a straight number series of days from
some arbritrary origin (1/1/2000 would always produce positive numbers
in my app). Seems a bit of an overkill though.
This must be a common enough problem? What do other people do?
Jochem van Dieten wrote:
>
> Richard Meredith-Hardy wrote:
> > In access one way of comparing whole days in a DateTime field is with
> > DateValue() thus:
> >
> > SELECT X,Y,Z
> > WHERE datevalue(UPDATED) = #createOdbcDate(mydate)#
> >
> > unfortunately the dateValue() function doesn't exist in MS SQL so
> > another way to do it is:
> >
> > SELECT X,Y,Z
> > WHERE datediff(d,UPDATED,#createOdbcDate(mydate)#) = 0
>
> > My question: Is this an effecient way of doing it, or is there a better
> > way?
>
> No. It is very unlikely that any database will be able to use an
> index for that query (look at the query plan to know for sure).
> From the perspective of performance it is probably better to use:
>
> SELECT X,Y,Z
> WHERE UPDATED BETWEEN <cfqueryparam cfsqltype="cf_sql_date"
> value="#mydate#"> AND <cfqueryparam cfsqltype="cf_sql_date"
> value="#DateAdd('d', 1, mydate)#">
>
> Jochem
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
