Any SQL gurus here?

[waz67]

I have a datetime field and an autoincrement ID field (this is MS SQL Server). I'm doing a select and I want to order by the date portion of the datetime, ignoring the time portion, like this...

select .... order by mydatetimefield desc, ID

but ignore the time in the mydatetimefield. Any ideas?

I found this...

cast(CONVERT(varchar(8), mydatetimefield, 112) AS datetime

which converts it to just a date, but was thinking if I start ordering by that, it might slow things down a lot...

 

[vdd]

I can't understand you. Please provide example and prefered output.

 

[shaggz]

you don't have to recast it....you can just do......

CONVERT(varchar(8), yourDateField, 112)

.......and it will still sort the correct way. That should save you a little bit of CPU time.

I take it you're doing something like.........

select amount, datefield from
myTable
order by CONVERT(varchar(8), yourDateField, 112) asc, amount desc

.........and the totally unique dates would kill the secondary order by

 

[waz67]

shaggz: yes, basically, I have a secondary order by which is getting messed up by the time portion of the datetime field, so I want to ignore the time and just order by the date portion.

 

[shaggz]

A couple of other things you could do:

a) add another column that is text based and indexed, and do the sort on that. To keep from altering existing insert queries, you could even define a trigger on an insert that would populate the new field for you.

b) use a feature called 'indexed views' for your query. An indexed view would allow you a faster runtime query performance because it would constantly update the views' index. However, inserts and updates would slow down.

more info on (b):

Creating Indexed Views

 

[paycoguy]

I haven't checked this this works, but maybe you can use the date function to retrieve the date

DATE([I]expr[/I]) 

Extracts the date part of the date or datetime expression[I]
expr[/I].         
 mysql> [B]SELECT DATE('2003-12-31 01:02:03');[/B]
        -> '2003-12-31'

So maybe have something like this?

SELECT DATE(mydatetimefield) as datetime from table order by datetime desc, id

 

[waz67]

Thanks guys for the tips, I'll give them a try!