Urgent please - I need to compare dates stored in
A user wanted to filter rows where a DATETIME column (aud_fecha) is earlier than a given timestamp (2011-07-30 12:32:43.380), but his query kept returning rows later than that time, including when using EXTEND...YEAR TO MINUTE. Jonathan Leffler suggested SQL-92 join syntax and step-by-step debugging (plain COUNT(*) on the single table, then with the join) to isolate whether the problem was the filter, the join, or the GROUP BY/aggregates, and asked for the IDS version, platform and table schemas (possible join-column type mismatch). Jack Parker suggested simply comparing against a quoted string literal. The thread ends with the poster restating the requirement; no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello forum people!
I need to compare two datetime fields to obtain records that are less than a
given datetime.
I tried to extend the function and also to declare an explicit datetime
comparison, but I get the desired results.
I know I can not be ignored.
I copy the example to see if anyone can help me.
select sm.iddeposito, sm.idproducto, p.descripcion, round( sum( sm.cant_disp
), 2 ) cantidad, round( p.costo, 2 ) costo
from stock_mov sm, productos p
where sm.aud_fecha < datetime( 2011-07-30 12:32:43.380 ) year to fraction(3)
and sm.idstatus != 0
and sm.idproducto = p.idproducto
and sm.iddeposito in ( 1, 3, 31, 34 )
group by 1,2,3,5
order by 1,2
On Thu, Oct 20, 2011 at 16:45, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> I need to compare two datetime fields to obtain records that are less than
> a
> given datetime.
> I tried to extend the function and also to declare an explicit datetime
> comparison, but I get the desired results.
> I know I can not be ignored.
> I copy the example to see if anyone can help me.
>
> select sm.iddeposito, sm.idproducto, p.descripcion, round( sum(> sm.cant_disp
> ), 2 ) cantidad, round( p.costo, 2 ) costo
> from stock_mov sm, productos p
> where sm.aud_fecha < datetime( 2011-07-30 12:32:43.380 ) year to
> fraction(3)
> and sm.idstatus != 0
> and sm.idproducto = p.idproducto
> and sm.iddeposito in ( 1, 3, 31, 34 )
> group by 1,2,3,5
> order by 1,2;
>
That looks OK; what is the effect you are seeing? Is there an error
message, or just an absence of results?
You should learn to use the SQL-92 explicit join notation, though:
SELECT sm.iddeposito, sm.idproducto, p.descripcion,
ROUND(SUM(sm.cant_disp), 2) AS cantidad, ROUND(p.cost, 2) AS costo
FROM Stock_Mov SM JOIN Producto AS p ON sm.idproducto = p.idproducto
WHERE sm.idstatus != 0
AND sm.iddeposito IN (1, 3, 31, 34)
AND sm.aud_fecha < DATETIME(2011-07-30 12:32:43.380) YEAR TO FRACTION
GROUP BY 1, 2, 3, 5
ORDER BY 1, 2;
I'd format it slightly differently in a fixed-width font, but that looks
best in a variable-width font.
Have you tried just selecting the Stock_Mov table to see whether you've got
any relevant data in it?
SELECT COUNT(*)
FROM Stock_Mov AS sm
WHERE sm.idstatus != 0
AND sm.iddeposito IN (1,3, 31, 34)
AND sm.aud_fecha < DATETIME(2011-07-30 12:32:43.380) YEAR TO FRACTION;
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--00151773e5e8484aae04afc4165a
Why not just:
> where sm.aud_fecha < '2011-07-30 12:32:43.380'
What is the datatype of sm.aud_fecha?
j.
On Oct 20, 2011, at 7:45 PM, GUSTAVO ECHENIQUE wrote:
> Hello forum people!=20
> I need to compare two datetime fields to obtain records that are less =
than a=20
> given datetime.=20
> I tried to extend the function and also to declare an explicit =
datetime=20
> comparison, but I get the desired results.=20
> I know I can not be ignored.=20
> I copy the example to see if anyone can help me.=20
>=20
> select sm.iddeposito, sm.idproducto, p.descripcion, round( sum( =sm.cant_disp=20
> ), 2 ) cantidad, round( p.costo, 2 ) costo=20
>=20
> from stock_mov sm, productos p=20
>=20
> where sm.aud_fecha < datetime( 2011-07-30 12:32:43.380 ) year to =
fraction(3)=20
>=20
> and sm.idstatus !=3D 0=20
>=20
> and sm.idproducto =3D p.idproducto=20
>=20
> and sm.iddeposito in ( 1, 3, 31, 34 )=20
>=20
> group by 1,2,3,5=20
>=20
> order by 1,2=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
First, thanks for yor response! The aud_fecha column is a DATETIME type. Regards! Gustavo
First, thanks for your response Johnathan. The first query returns the same results as the original query. The second query returns more than 175,000 records. Do not understand. Regards! Gustavo Echenique
On Thu, Oct 20, 2011 at 18:49, GUSTAVO ECHENIQUE <
gustavo.echenique@cemdo.com.ar> wrote:
> First, thanks for your response Jonathan.
>
> The first query returns the same results as the original query.
>
> The second query returns more than 175,000 records.
>
> Do not understand.
>
Nor me, yet.
Please include enough context for your response to make sense.
On Thu, Oct 20, 2011 at 16:45, GUSTAVO ECHENIQUE <
> gustavo.echenique@cemdo.com.ar
> > wrote:
>
> > I need to compare two datetime fields to obtain records that are less
> than
> > a given datetime.
> > I tried to extend the function and also to declare an explicit datetime
> > comparison, but I get the desired results.
> > I know I can not be ignored.
> > I copy the example to see if anyone can help me.
> >
> > select sm.iddeposito, sm.idproducto, p.descripcion, round( sum(> > sm.cant_disp
> > ), 2 ) cantidad, round( p.costo, 2 ) costo
> > from stock_mov sm, productos p
> > where sm.aud_fecha < datetime( 2011-07-30 12:32:43.380 ) year to
> > fraction(3)
> > and sm.idstatus != 0
> > and sm.idproducto = p.idproducto
> > and sm.iddeposito in ( 1, 3, 31, 34 )
> > group by 1,2,3,5
> > order by 1,2;
> >
>
> That looks OK; what is the effect you are seeing? Is there an error
> message, or just an absence of results?
>
> You should learn to use the SQL-92 explicit join notation, though:
>
> SELECT sm.iddeposito, sm.idproducto, p.descripcion,> ROUND(SUM(sm.cant_disp), 2) AS cantidad, ROUND(p.cost, 2) AS
> costo
> FROM Stock_Mov SM JOIN Producto AS p ON sm.idproducto = p.idproducto
> WHERE sm.idstatus != 0
> AND sm.iddeposito IN (1, 3, 31, 34)
> AND sm.aud_fecha < DATETIME(2011-07-30 12:32:43.380) YEAR TO FRACTION
> GROUP BY 1, 2, 3, 5
> ORDER BY 1, 2;
>
> I'd format it slightly differently in a fixed-width font, but that looks
> best in a variable-width font.
>
> Have you tried just selecting the Stock_Mov table to see whether you've got
> any relevant data in it?
>
> SELECT COUNT(*)
> FROM Stock_Mov AS sm
> WHERE sm.idstatus != 0
> AND sm.iddeposito IN (1,3, 31, 34)
> AND sm.aud_fecha < DATETIME(2011-07-30 12:32:43.380) YEAR TO FRACTION;>
So, the SELECT COUNT(*) returns about 175K as the count.
The next step must be a join with your Producto table:
SELECT COUNT(*)
FROM Stock_Mov AS sm JOIN Producto AS p ON sm.idproducto = p.idproducto
WHERE sm.idstatus != 0
AND sm.iddeposito IN (1,3, 31, 34)
AND sm.aud_fecha < DATETIME(2011-07-30 12:32:43.380) YEAR TO FRACTION;
If the join works, it should also return 175K or thereabouts, unless you
maintain rows in your Stock_Mov table that don't correspond to products in
your Producto table.
If that works, then the problem is in the grouping and computations on
aggregates, it would seem. If that fails, then the problem is in the basic
joining.
You've not identified which version of IDS you are using, nor which platform
it is running on. That information might be helpful. The schema for the
two tables (or, at least, for the columns that are mentioned in the queries)
would be a help too. If there's a type mismatch in the join columns, for
instance, you can end up with odd results.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--00151774036a90364b04afc7a4c2
Ok Jonathan, you're right, I'll add the context to make it more clear it. On 31/07/2010 at my company did the material balance on deposit. I need to take only the input records materials that day. As I know that the outputs are recorded from the 12:32, I have to select those that are prior to that time. The problem is that if I use the function extend -> year to minute, it takes all movements, including those who are superior at 12:32.