Unique registry with date data is weird sorted.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Unique registry with date data is weird sorted.

Author

Topic

chorofonfilo
Starting Member

40 Posts

Posted - 2007-10-26 : 16:49:06

Hello to everyone there, this is my first post here, is really cool to learn and help others through online communities, thanks to everyone.

I developed an asp.net application and i am storing a date on a field ussing the traditional date.now() function, which loads the value on a sql 2000 table which has a datetime type column, so it stores something like this:

2007-10-02 10:42:24.423

So here is when the weird part comes, when i sort it ussing order by either on asc or desc way, ussing this procedure.

alter procedure damedatosrequerimientos
as

Select rq.idreq as '#OM',a.nomarea as 'Area Solicitante',us.nomapeuser as 'Solicitante',convert(varchar,datepart(dd,rq.fechagenreq))+'/'+convert(varchar,datepart(mm,rq.fechagenreq))+'/'+convert(varchar,datepart(yy,rq.fechagenreq) as 'Fecha',p.nomprio as 'Prioridad',m.nomot as 'Motivo',e.nomestado as 'Estado' from requerimientom rq,areas a,usuarios us,prioridad p,motivo m,estado e
where rq.idarea=a.idarea and rq.iduser=us.iduser and rq.idmot=m.idmot and rq.idprioridadr=p.idprio and rq.idestado=e.idestado
order by Fecha desc

#OM Area Solicitante Solicitante Fecha(field sorted)

00034 Servicio Técnico Jose Ruiz 21/10/2007

00034 Servicio Técnico Jose Ruiz 21/10/2007

00032 Servicio Técnico Jose Ruiz 2/10/2007<----

00033 Sistemas Franco Alva 18/10/2007

00004 Sistemas Alvaro Jonas 1/10/2007

00003 Ventas-Imagenes-ICR Alvaro Jonas 1/10/2007

Note that the 2/10 should appear first than the 21/10 since its meant to be sorted on the desc way, the other registries are well sorted, except this particular one.

The only way i had to take out just the date from the field, which also contains the time, was the one described on the query below, since i dont know an sql function that can actually just extract the date from a field that contains both, date and time, so i figured out this way to do it, but now i have this problem, which is "interesting".

I have the spanish version of the sql server 2000, i might be doing something wrong while ussing convert function?.

What can i do to sort it on the correct form.

Thank you very much for your help, and for your time once more :).

Cheers.

Choro.

Perseverance worths it...:)

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-10-27 : 00:41:51

You can use this to get a datetime where the time is 00:00

DATEADD(dd, DATEDIFF(dd, 0, rq.fechagenreq), 0)

Then to convert the date to a string in various formats (with or without time) use the date style option with the CONVERT function, something like

CONVERT(varchar(10), rq.fechagenreq, 103)

See
http://msdn2.microsoft.com/en-us/library/ms187928.aspx

Don't convert the date to a string in the ORDER BY though, just use
ORDER BY DATEADD(dd, DATEDIFF(dd, 0, rq.fechagenreq), 0) DESC
so that the rows are really sorted by the dates rather than by strings which sort "alphabetically", not "dately".

So your query becomes

SELECT 
    rq.idreq as [#OM]
    , a.nomarea as [Area Solicitante]
    , us.nomapeuser as [Solicitante]
    , CONVERT(varchar(10), rq.fechagenreq, 103) as [Fecha]
    , p.nomprio as [Prioridad]
    , m.nomot as [Motivo]
    , e.nomestado as [Estado] 
FROM requerimientom rq, areas a, usuarios us, prioridad p, motivo m, estado e
WHERE rq.idarea=a.idarea and rq.iduser=us.iduser and rq.idmot=m.idmot and rq.idprioridadr=p.idprio and rq.idestado=e.idestado
ORDER BY DATEADD(dd, DATEDIFF(dd, 0, rq.fechagenreq), 0) DESC

Unless of course you want the rows ordered by time as well, just not showing the time in the results, in which case this would be better

SELECT 
    rq.idreq as [#OM]
    , a.nomarea as [Area Solicitante]
    , us.nomapeuser as [Solicitante]
    , CONVERT(varchar(10), rq.fechagenreq, 103) as [Fecha]
    , p.nomprio as [Prioridad]
    , m.nomot as [Motivo]
    , e.nomestado as [Estado] 
FROM requerimientom rq, areas a, usuarios us, prioridad p, motivo m, estado e
WHERE rq.idarea=a.idarea and rq.iduser=us.iduser and rq.idmot=m.idmot and rq.idprioridadr=p.idprio and rq.idestado=e.idestado
ORDER BY rq.fechagenreq DESC

Final note - I strongly recommend that you use the JOIN keyword to join the tables, rather than just using a list of tables like you've done here, but I'm not going to change that for you, look here
http://msdn2.microsoft.com/en-us/library/ms177634.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources