How to get the time difference between two date stamps and arrange in DESCENDING ORDER

Question

I need the time difference between two datestamps in hours and need to arrange them in descending order to get the median

SELECT "fulfillmentshistories"."fulfillmentid" as "Fulfillment ID" FROM FULFILLMENTHISTORIES
WHERE (Fulfillments."status" IN ('paid','delivered') AND (DATE("fulfillmenthistories"."createdAt") BETWEEN DATE('{{startDate}}') AND DATE('{{endDate}}')))
union
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF;
ORDER BY DATEDIFF, DESC

This is what I tried please help

SELECT "fulfillmentshistories"."fulfillmentid" as "Fulfillment ID" FROM    FULFILLMENTHISTORIES
WHERE (Fulfillments."status" IN ('paid','delivered') AND (DATE ("fulfillmenthistories"."createdAt") BETWEEN DATE('{{startDate}}') AND DATE('{{endDate}}')))
union
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF;
ORDER BY DATEDIFF, DESC

Error running query: syntax error at or near "ORDER" LINE 5: ORDER BY DATEDIFF, DESC ^

`as DATEDIFF; ORDER BY DATEDIFF, DESC` should be `as DATEDIFF ORDER BY DATEDIFF DESC` — Arulkumar, Jun 10 '19 at 05:44

score 0 · Answer 1 · edited Jun 10 '19 at 06:47

0

SELECT DATEDIFF(HOUR, 'startDate'-'endDate') as DATEDIFF; ORDER BY DATEDIFF, DESC

should be

SELECT DATEDIFF(HOUR, 'startDate'-'endDate') as DATEDIFF ORDER BY DATEDIFF, DESC ;

and try avoiding alias names similar to the function name.

edited Jun 10 '19 at 06:47

saurabh

2,098
1
16
25

answered Jun 10 '19 at 05:59

Anurag Singhal

1
2

I dont see any change yet – Kaushika Potluri Jun 11 '19 at 05:50
Error running query: syntax error at or near "DESC" LINE 4: ...'startDate'-'endDate') as DATEDIFF ORDER BY DATEDIFF, DESC ; ^ This is the error im getting. – Kaushika Potluri Jun 11 '19 at 05:51
IS IT NECESSARY TO HAVE A FROM CLAUSE FOR THE SECOND SELECT STATEMENT TOO? – Kaushika Potluri Jun 11 '19 at 05:55

score 0 · Answer 2 · answered Jun 10 '19 at 06:58

UNION
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF;
ORDER BY DATEDIFF, DESC

should be

UNION
SELECT DATEDIFF(HOUR,   'startDate'-'endDate') as DATEDIFF
ORDER BY DATEDIFF DESC;

Remove the semicolon after DATEDIFF and the comma after ORDER BY DATEDIFF. The semicolon should come at the end of the SQL statement since the whole of that is one sql statement.

How to get the time difference between two date stamps and arrange in DESCENDING ORDER

2 Answers2