Solved

# Order by Date field in SQL

Posted on 2012-09-05
493 Views
Hi,

Below is the sql query
``````DECLARE @Calendar_Details TABLE
(
[CalendarID] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[FinishDate] [datetime] NULL,
[Provisional_Date] [datetime] NULL
)
INSERT INTO @Calendar_Details(StartDate,FinishDate,Provisional_Date)
SELECT '2017-06-25 00:00:00.000','2017-09-09 00:00:00.000','2017-08-30 00:00:00.000' UNION ALL
SELECT '2017-08-01 00:00:00.000',NULL,'2017-08-01 00:00:00.000' UNION ALL
SELECT NULL,NULL,'2017-08-01 00:00:00.000'

SELECT DateData
FROM
(
Select Case
When isnull(FinishDate,'') = '' and isnull(StartDate,'') = ''
then convert(varchar(4), LEFT(DATENAME(mm, Provisional_Date), 3))
when isnull(FinishDate,'') = ''
then convert(varchar(2), day(StartDate)) + ' ' + convert(varchar(4), LEFT(DATENAME(mm, StartDate), 3))
ELSE convert(varchar(2), day(StartDate)) + ' ' + convert(varchar(4), LEFT(DATENAME(mm, StartDate), 3))
END AS DateData,ISNULL(StartDate,Provisional_Date) AS DD
From @Calendar_Details
) AA
ORDER BY DD
``````

output :
25 Jun
1 Aug
Aug

My query is using the above query how to get output as below

Expected output

25 Jun
Aug
1 Aug

Shabbir
0
Question by:sportcal

LVL 25

Accepted Solution

The last two rows have the same date ... 1st Aug in DD.  The order they get sorted into will be arbitrary.  You need another sort column ... try

ORDER BY DD, DateData DESC
0

LVL 9

Expert Comment

REmove another case statement with when as it will automatically went to else condition and both are same...so now your revised code will be...

DECLARE @Calendar_Details TABLE
(
[CalendarID] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[FinishDate] [datetime] NULL,
[Provisional_Date] [datetime] NULL
)
INSERT INTO @Calendar_Details(StartDate,FinishDate,Provisional_Date)
SELECT '2017-06-25 00:00:00.000','2017-09-09 00:00:00.000','2017-08-30 00:00:00.000' UNION ALL
SELECT '2017-08-01 00:00:00.000',NULL,'2017-08-01 00:00:00.000' UNION ALL
SELECT NULL,NULL,'2017-08-01 00:00:00.000'

SELECT DateData
FROM
(
Select Case
When isnull(FinishDate,'') = '' and isnull(StartDate,'') = ''
then convert(varchar(4), LEFT(DATENAME(mm, Provisional_Date), 3))
ELSE convert(varchar(2), day(StartDate)) + ' ' + convert(varchar(4), LEFT(DATENAME(mm, StartDate), 3))
END AS DateData,ISNULL(StartDate,Provisional_Date) AS DD
From @Calendar_Details
) AA
ORDER BY dd,DateData desc
0

Author Closing Comment

Thank you very much
0

## Featured Post

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…