Order By Date Format

I have inherited some code with a view that formats dates like
Mar 26, 2003
May 06, 2006
Jun 05, 2006
Example
SELECT DISTINCT TOP 5000 createDate, responsibilities
FROM vw_someview
ORDER BY createDate DESC,responsibilities
the order by doesnt come out right. The view select for the field is
convert( varchar(50), pp.createDate, 107 ) as createDate,
It is stored as 2003-03-26 14:29:41.880
Id like to order by properly but was wondering if I can do another
convert to make it order by properly?
Thanksuse order by convert(datetime,CreateDate)
take a look at these 2 example
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by CreateDate
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by convert(datetime,CreateDate)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Brian wrote:
> I have inherited some code with a view that formats dates like
> Mar 26, 2003
> May 06, 2006
> Jun 05, 2006
> Example
> SELECT DISTINCT TOP 5000 createDate, responsibilities
> FROM vw_someview
> ORDER BY createDate DESC,responsibilities
> the order by doesnt come out right. The view select for the field is
> convert( varchar(50), pp.createDate, 107 ) as createDate,
> It is stored as 2003-03-26 14:29:41.880
> Id like to order by properly but was wondering if I can do another
> convert to make it order by properly?
> Thanks|||Brian,
Sounds to me like your view needs changing, to report the date as a date.
How about leaving the formatting until you need to display it?
Currently, you're converting the date into a string and then querying that,
and then trying to sort by the result of a function on that string. That's
messy. Instead, let your view report the date itself, and tell whatever uses
that view to display the date in an appropriate format. If you really need
the view to report the date as string, then put it in a different column.
Of course, refactoring like this will be so much nicer in Visual Studio Team
Edition for Database Professionals, because you should be able to rename a
column in a view (to, say, CreateDateFormatted), and have it run through
your solution and do all the renaming for you.
Rob
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149525868.872085.191850@.i40g2000cwc.googlegroups.com...
> use order by convert(datetime,CreateDate)
> take a look at these 2 example
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by CreateDate
>
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by convert(datetime,CreateDate)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Brian wrote:
>