achive a distinct with convert involved

hi all
how can achive this query?


select distinct fechap from cheque  order by Convert(Datetime, fechap, 103)desc

is a varchar that i need to convert in date to sort

tsm
ErnestoAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
So let's start from the beginning:  

What is the data type for "fechap"?  If it is varchar (or nvarchar) and has the format ddmmyyyy then I would suggest a minor change to the solution provided (no points please):
SELECT	DISTINCT
        CONVERT(Datetime, fechap, 103) fechap2
FROM    cheque
ORDER BY 
	fechap2 DESC

Open in new window

Or: 
SELECT	DISTINCT
        CONVERT(Datetime, c.fechap, 103) fechap
FROM    cheque c
ORDER BY 
	fechap DESC

Open in new window

Or: 
SELECT	DISTINCT
        CONVERT(Datetime, fechap, 103) fechap2
FROM    cheque
ORDER BY 
	1 DESC

Open in new window

0
 
CodeCruiserCommented:
And is it not working? You need a space between ")" and "desc"
0
 
ErnestoAuthor Commented:
has it

tsm
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Access It Now
 
CodeCruiserCommented:
and...
0
 
ErnestoAuthor Commented:
any way to achive it?
0
 
ErnestoAuthor Commented:
do not work
0
 
CodeCruiserCommented:
You get any error?
0
 
ErnestoAuthor Commented:
Cuando se especifica SELECT DISTINCT, los elementos de ORDER BY deben aparecer en la lista de selección.

when an select distinct is specified, the elements of the order by must appear in the list
0
 
ErnestoAuthor Commented:
any advice?
tsm
0
 
CodeCruiserCommented:
Try

select distinct Convert(Datetime, fechap, 103) from cheque  order by Convert(Datetime, fechap, 103) desc
0
 
Anthony PerkinsCommented:
edo60,

While most of us here have a lot of knowledge with SQL Server, none of us (to my knowledge) can read a crystal ball, so it is in your best interest to explain fully what you need as we are not sitting in front of your computer.  Quite frankly, stating "do not work" is no use whatsoever.  Just imagine taking your car to the mechanic telling them "do not work" and I suspect you should understand.

For clarity here is the same in Spanish:
Mientras hay muchos aquí que son muy capaces usando SQL Server, nadie (de que yo sepa) somos magos, por lo tanto por tu bien te conviene explicar completamente tus requerimientos ya que no estamos enfrente de tu computadora (ordenador).  Francamente diciendo "no funciona" no ayuda en lo más mínimo.  Imagina que lleves tu auto al mecánico y le dices" "no funciona" y creo que comprenderás.
0
 
ErnestoAuthor Commented:
Ok, im understand what you say, sorry for the language barrear, but you give me greate solution with

SELECT      DISTINCT
        CONVERT(Datetime, c.fechap, 103) fechap
FROM    cheque c
ORDER BY
      fechap DESC

it give me the result that i want, only i detail, is any way to put off the time?
the result is:

2013-01-04 00:00:00.000
2013-01-03 00:00:00.000
2013-01-02 00:00:00.000
2012-12-21 00:00:00.000
2012-12-19 00:00:00.000
2012-12-18 00:00:00.000
2012-12-13 00:00:00.000
2012-12-11 00:00:00.000

is the order that i need but is posible to put off 00:00:00.000?
tsm
regards
0
 
ErnestoAuthor Commented:
tsm
regards
0
 
Anthony PerkinsCommented:
If you have SQL Server 2008 you can do this:
CONVERT(Date, c.fechap, 103) fechap

If not you can do this (just understand that it is now character data):
CONVERT(char(10), CONVERT(Datetime, c.fechap, 103), 120) fechap
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here