Subject Re: [firebird-support] Re: Comparing Time portion of Date in query
Author Martijn Tonies
> > > Does anyone know how to compare only the Time portion of a Date
> > > field? If just run query "Select * from MyTable where Time1 <
> Time2"
> > > then will rely on both Date and Time (so if Time1 has date of
> > > 1/1/2003 and Time2 has date of 1/1/2005 then will think Time1 is
> > > less than Time2, regardless of the Time portion of date).
> > >
> > > Only way I could think of to do this was to convert the time to a
> > > string and then compare. Tried the following but both failed:
> > >
> > > 1) Cast to a char - but dont know how to get just Time part.
> > > The "Cast(Time1, char(20))" will give me something like "01-JAN-
> 2003
> > > 14:04:06". This would work if could just get last 8 chars. I
> tried
> > > to use SubStr, but interbase did not recognize this keyword.
> >
> > What's wrogn with:
> >
> > WHERE cast(Time1 as TIME) < cast(Time2 as TIME)
>
> Actually, I forgot to mention that I did try that. I get error:
>
> "Client SQL dialect 1 does not support reference to TIME datatype".

Ah, right ... you're using Dialect 1.

Well, as you noticed, Dialect 1 doesn't do "time". I think the best
bet is using an UDF to transform the time part into a value you
can compare.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com