Convert Date and order by

Hi!

I have a little problem. I'm trying to sort a date I have converted like this
Convert(datetime,LH.LoginDateTime,103) as RegistrationDate

But when I use Order by on RegistrationDate it only sort on days:

01/11/2006
01/12/2006
02/11/2006
02/12/2006
03/11/2006
03/12/2006

I'll guess it's because of the "varchar" convert, but I need the date to bee inn this format, since I only shall check the date and not the time. Is there a way around this, so I can order it like this? (Se under)

01/11/2006
02/11/2006
03/11/2006
01/12/2006
02/12/2006
03/12/2006

Sample SQL;

select Convert(varchar,LH.LoginDateTime,103) as RegistrationDate,
select count(*) from LoginHistory AS LH2 where datepart(hh,LH2.LoginDateTime)<7 AND
Convert(varchar,LH2.LoginDateTime,103)>=Convert(varchar,LH.LoginDateTime,103) AND
Convert(varchar,LH2.LoginDateTime,103)<=Convert(varchar,LH.LoginDateTime,103))
As beforehour07
from LoginHistory AS LH
where LH.LoginDateTime >='''+ Convert(varchar,@.FromDate,113) + ''' ' +
'and LH.LoginDateTime <='''+ Convert(varchar,@.ToDate,113) + ''' ' +
'group by Convert(varchar,LH.LoginDateTime,103)'
Order by RegistrationDate

Hi there,

Why dont you just order by the LH.LoginDateTime field?

thanks,

Murthy here

|||

You should also look on your count select statement because it will not work correctly comparing strings not dates, try this if you would like to use days only without time:

select Convert(varchar,LH.LoginDateTime,103) as RegistrationDate,
(select count(*) from LoginHistory AS LH2 where datepart(hh,LH2.LoginDateTime)<7 AND
convert(datetime,Convert(varchar,LH2.LoginDateTime,103))>=convert(datetime,Convert(varchar,LH.LoginDateTime,103)) AND
convert(datetime,Convert(varchar,LH2.LoginDateTime,103))<=convert(datetime,Convert(varchar,LH.LoginDateTime,103)))
As beforehour07
from LoginHistory AS LH
where LH.LoginDateTime >='''+ Convert(varchar,@.FromDate,113) + ''' ' +
'and LH.LoginDateTime <='''+ Convert(varchar,@.ToDate,113) + ''' ' +
'group by Convert(varchar,LH.LoginDateTime,103)'
Order by convert(datetime,RegistrationDate)

or you can try to rewrite your query to work much faster with single join instead of multiple internal queries to do counts.

This one will kill your server if you will try to process table with thousands of records

|||Thanks for the answer!

If I order by LH.LoginDateTime, I have to select from LH.LoginDate and group by LH.LoginDate or else I get the error:
"Column "LoginDateTime" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause." since the?dates don't match anymore.

If I put LH.LoginDateTime in the select,group and Order by, the dates on the same day won't match so the group by won't hit. I will get a new row for every hit on the same day like this:

01.11.2006 00:11:40
01.11.2006 00:15:41
01.11.2006 00:15:44
01.11.2006 00:16:48

I need to convert the date to dd/mm/yyyy(or something like that, removing the time) and be able to group and then order it. Do you or anyone know if it's possible?

Murthy Puvvada:

Hi there,

Why dont you just order by the LH.LoginDateTime field?

thanks,

Murthy here?

|||Fixed it, I could use cast(floor(cast(LH.LoginDateTime as float)) as datetime) to sett the time to 00:00:00