How to order this query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to order this query?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-10-30 : 05:24:35

Hi,

I am trying to order this query by date which is formatted as follows:
CONVERT(VARCHAR(10), et.ReturnDate, 103)

Here is the query:

select * from(select et.VendorNo,et.SeriNo,'ESKI LISTE' as Liste,CONVERT(VARCHAR(10), et.ReturnDate, 103) as 'Geri Alim Tarihi' from Eform_Terminal et inner join SerialOld s on s.SeriNo = et.SeriNo inner join MlotOld m on m.VendorNo = et.VendorNo where et.IsApproved in(0,1) union all select et.VendorNo,et.SeriNo,'YENI LISTE' as Liste,CONVERT(VARCHAR(10), et.ReturnDate, 103) as 'Geri Alim Tarihi' from Eform_Terminal et inner join SerialNew s on s.SeriNo = et.SeriNo inner join MlotNew m on m.VendorNo = et.VendorNo where et.IsApproved in(0,1)) as X order by "Geri Alim Tarihi"

Best Regards.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 05:37:18

[code]
select *
from(select et.VendorNo, et.SeriNo, 'ESKI LISTE' as Liste, CONVERT(VARCHAR(10), et.ReturnDate, 103) as 'Geri Alim Tarihi'
from Eform_Terminal et
inner join SerialOld s on s.SeriNo = et.SeriNo
inner join MlotOld m on m.VendorNo = et.VendorNo
where et.IsApproved in(0,1)

union all

select et.VendorNo, et.SeriNo, 'YENI LISTE' as Liste, CONVERT(VARCHAR(10), et.ReturnDate, 103) as 'Geri Alim Tarihi'
from Eform_Terminal et
inner join SerialNew s on s.SeriNo = et.SeriNo
inner join MlotNew m on m.VendorNo = et.VendorNo
where et.IsApproved in(0,1)
) as X
order by 'Geri Alim Tarihi'
[/code]

(OR)
[code]
select et.VendorNo, et.SeriNo, 'ESKI LISTE' as Liste, CONVERT(VARCHAR(10), et.ReturnDate, 103) as 'Geri Alim Tarihi'
from Eform_Terminal et
inner join SerialOld s on s.SeriNo = et.SeriNo
inner join MlotOld m on m.VendorNo = et.VendorNo
where et.IsApproved in(0,1)
union all
select et.VendorNo, et.SeriNo, 'YENI LISTE' as Liste, CONVERT(VARCHAR(10), et.ReturnDate, 103) as 'Geri Alim Tarihi'
from Eform_Terminal et
inner join SerialNew s on s.SeriNo = et.SeriNo
inner join MlotNew m on m.VendorNo = et.VendorNo
where et.IsApproved in(0,1)
order by 'Geri Alim Tarihi'[/code]

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-10-30 : 05:41:53

Convert with style 103 means dd/mm/yyyy.
Do you really want to order this way?

Too old to Rock'n'Roll too young to die.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-10-30 : 05:44:33

Hi,

They do NOT work! Here is the output:

22/08/2012
26/09/2012
28/07/2012
28/08/2012

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-10-30 : 05:45:02

@webfred yes I want it this way.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 06:21:24

Oh 'Geri Alim Tarihi' is in varchar format..........

Then
Order by right('Geri Alim Tarihi', 4) DESC,
substring('Geri Alim Tarihi', 4,2) DESC,
left('Geri Alim Tarihi',2) DESC

--
Chandu

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-10-30 : 07:16:14

@bandi thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-30 : 07:25:13

quote:
Originally posted by raysefo

@bandi thank you

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-30 : 09:35:55

why do you want to order dates after making it varchar? why not keep dates as it is and use just

order by OrderDate

why not do formatting at your presentation layer using format functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources