How to sort datetime in a ascending order through Sql-script.

Question

how can i sort the date time in a sql script. At first i am using the datetimeFormatter to get the date-time in a specific format. Later i am writing into a SQL table using a script. Below is my grovvy code.

def date = Finish / 1000;  ////here Finish is in java.lang.Long
LocalDateTime dateTime = LocalDateTime.ofEpochSecond(date, 0, ZoneOffset.UTC);
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("hh:mm:ss dd-MM-yyyy", Locale.ENGLISH);
String formattedDate = dateTime.format(formatter);
main.setdoneTime(formattedDate);

In a sql table i have a column called Timedone having varchar(255) format. I am trying to order the Timedone in a ascending order, through a script,

select * from [devnew].[dbo].[Table1] WHERE Total!= Right + Left order by Timedone

only the time is getting sorted but not the date, month or year. how can i make both sorted.

You've stored it as a string & want to sort it by date/time I think, not sure of your underlying database (sql server maybe?) but you could try converting to date e.g. `...order by to_char( message_date, 'YYYY-MM-DD HH24:MI:SS' )` that's Oracle — Mike W, May 30 '18 at 12:47
or you should change the date format to `yyyy-MM-dd hh:mm:ss` or before sorting convert strings to date on the level of database. the function that converts string to date depends on database. in Oracle - it's [`to_date`](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm) — daggett, May 30 '18 at 13:24

score 0 · Answer 1 · answered Jun 08 '18 at 06:03

0

You need to add date column in ORDER BY clause

We know that you have TimeDone field in your table

Let's assume you have also DateDone field, too

Then modify the ORDER BY clause as follows:

order by DateDone, Timedone

answered Jun 08 '18 at 06:03

Eralper

6,461
2
21
27

How to sort datetime in a ascending order through Sql-script.

1 Answers1