[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

Order by Date field in SQL

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

Open in new window


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
 
Thanks in Advance

Shabbir
0
sportcal
Asked:
sportcal
1 Solution
 
lwadwellCommented:
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
 
keyuCommented:
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
 
sportcalAuthor Commented:
Thank you very much
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now