smalldatetime and sorting

[sunshinevaldes]

Hello all and thank you for your time.

I have a datagrid where I want to view dates (smalldatetime) and be
able to sort on the field. However, I do not want to have the time
part show. If I use the convert function(convert(char,ProjDate,101) as
ProjDate) the field is then a char and does not sort correctly.

Any solutions?

Thanks again,
Sunshine

[Simon Hayes]

<su************@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Hello all and thank you for your time.

I have a datagrid where I want to view dates (smalldatetime) and be
able to sort on the field. However, I do not want to have the time
part show. If I use the convert function(convert(char,ProjDate,101) as
ProjDate) the field is then a char and does not sort correctly.

Any solutions?

Thanks again,
Sunshine

I don't know much about datagrids, but you might find it easier to do this
in the datagrid, not the server. To get dates to sort correctly using
CONVERT(), you would have to do something like this (there may be other
formats which work too):

convert(char(8), ProjDate, 112)

That will always sort correctly, but it may not be in the format your users
want or expect, especially if you need to support international locations
with different locales. In general, any sort of formatting and presentation
is best done in the front end, and that's especially true of things that
depend on a user's locale, so I would investigate what you can do in the
datagrid itself.

Simon

[sunshinevaldes]

I ended up just pulling the data as a smalldatetime anyway. In the
datagrid column, I used {0:MM/dd/yyyy} to format the date and it works
fine.

Sunshine

[Erland Sommarskog]

(su************@yahoo.com) writes:

I ended up just pulling the data as a smalldatetime anyway. In the
datagrid column, I used {0:MM/dd/yyyy} to format the date and it works
fine.

I don't know the datagrid, but you should really just set up the date
format according to the regional settings in Windows, and not hard-code a
format. (And if you hard-code a format, you should use YYYY-MM-DD and
not one that is likely to misunderstood in most countries in the world.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp