Can any one explain the reason why results are different?

Question

0.00/5 (No votes)

See more:

I m having a DB table, having records as below:

id  letterdate
1    2015-02-23 18:43:00.000
2    2015-02-27 10:36:00.000
3    2015-02-23 18:43:00.000
4    2015-02-23 18:43:00.000
5    2015-02-27 10:36:00.000
6    2015-02-27 10:36:00.000
7    2015-02-27 10:36:00.000
8    2015-02-27 10:36:00.000
9    2015-02-27 10:36:00.000
10   2015-02-23 18:43:00.000
11   2015-02-27 10:36:00.000
12   2015-02-27 10:36:00.000
13   2015-02-23 18:43:00.000
14   2015-02-27 10:36:00.000
15   2015-02-27 10:36:00.000
16   2015-02-23 18:43:00.000
17   2015-02-27 10:36:00.000
18   2015-02-27 10:36:00.000
19   2015-02-27 10:36:00.000
20   2015-02-27 10:36:00.000
22   2015-02-23 18:43:00.000
23   2015-02-27 10:36:00.000
24   2015-02-27 10:36:00.000
25   2015-02-27 10:36:08.000
26   2015-02-23 19:26:33.000
27   2015-03-01 16:42:10.000
28   2015-03-01 16:42:10.000
29   2015-03-01 16:42:10.000
30   2015-03-01 16:42:10.000
31   2015-03-01 16:42:10.000
32   2015-03-01 16:42:10.000
33   2015-03-01 16:42:10.000
34   2015-03-01 16:42:10.000
35   2015-03-01 16:42:10.000
36   2015-03-01 16:42:10.000
37   2015-03-01 16:42:10.000
38   2015-03-01 16:42:10.000
39   2015-03-01 16:42:10.000
40   2015-03-01 16:42:10.000
41   2015-03-01 16:42:10.000
42   2015-03-01 16:42:10.000
43   2015-03-01 16:42:10.000
44   2015-03-01 16:42:10.000
45   2015-03-01 16:42:10.000
46   2015-03-01 16:42:10.000
47   2015-03-01 16:42:10.000
48   2015-03-01 16:42:10.000
49   2015-03-01 16:42:10.000
50   2015-03-01 16:42:10.000
51   2015-03-01 16:42:10.000
52   2015-03-01 16:42:10.000
53   2015-03-02 14:34:31.000
54   2015-03-02 14:34:31.000
55   2015-03-02 14:34:31.000
56   2015-03-02 14:34:31.000
57   2015-03-02 14:34:31.000
58   2015-03-02 13:25:59.000
59   2015-03-02 13:26:00.000
60   2015-03-02 13:26:01.000
61   2015-03-11 15:07:20.000
62   2015-03-11 13:51:15.000
63   2015-03-11 13:39:05.000
64   2015-03-11 13:39:05.000
65   2015-03-12 17:31:21.000

below queries gives me different result.

declare @RefCode nvarchar(50)='ALP002';

Query 1:

SQL

with dates AS
(
   SELECT DISTINCT cast([LetterDate] AS date) AS
 LetterDate
 FROM t_LettersForPrint where isactive=1  and RefCode=@RefCode
)

 SELECT
 convert(char(12), LetterDate, 103) AS LetterDate
 FROM dates order by LetterDate desc;

Result:
LetterDate
27/02/2015
23/02/2015
11/03/2015
02/03/2015
01/03/2015

Query 2:

SQL

with dates AS
(
   SELECT DISTINCT cast([LetterDate] AS date) AS
 LetterDate
 FROM t_LettersForPrint where isactive=1  and RefCode=@RefCode
)

 SELECT
 convert(char(12), LetterDate, 103) AS LD
 FROM dates order by LetterDate desc

Result:
LD
11/03/2015
02/03/2015
01/03/2015
27/02/2015
23/02/2015

There is only one difference in both queries i.e. alias of column.
Can any one explain the reason why results are different?

Note: second one full fills my requirement.

Posted 16-Mar-15 3:45am

tanishtaman

Add a Solution

3 solutions

Solution 1

In the first option the alias of the date-casting is LetterDate and you order by that field, but in the second query the alias is LD and you still ordering by LetterDate!

Posted 16-Mar-15 3:56am

Kornfeld Eliyahu Peter

Updated 16-Mar-15 4:05am

v2

Solution 3

You first query sorting the results by char(12) but second one is sorting by date because the first query taking the alias for sorting rather than from CTE

Posted 16-Mar-15 4:05am

John C Rayan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Praveen Kumar Upadhyay · Accepted Answer · 2015-03-16T03:58:00

First query is

SQL

SELECT
  convert(char(12), LetterDate, 103) AS LetterDate
  FROM dates order by LetterDate desc

In the above query your are converting LatterDate to char and setting alias as LetterDate. Now your LatterDate became char column and that is why when you are doing order by, it is not giving you result order by LatterDate(expected date wise), instead it is giving result order by LatterDate(as it now became char).

I hope I answered your query.