lydias
lydias

Reputation: 841

SQL convert string in the form of yyyy-MM-ddThh:mm:ssZ to datetime

I have a variable in the format of string, such as 2019-11-25T14:22:56.529Z. And I need to extract the date part of this string variable and extract all records with the latest date.

ID   time (in string format, not a timestamp yet)
-------------------------------
1    2019-11-25T15:20:56.539Z
2    2019-11-25T04:23:56.522Z
3    2019-11-25T11:11:54.509Z
4    2019-12-25T10:22:36.129Z
5    2019-12-25T09:22:29.349Z
6    2019-12-25T08:22:56.209Z

I want this output:

ID   time
---------------
4    2019-12-25
5    2019-12-25
6    2019-12-25

In general, I would also like to know how to convert this type of string variable to date format for future sorting by datetime. Thank you!

Upvotes: 0

Views: 1863

Answers (2)

Ronak Dhoot
Ronak Dhoot

Reputation: 2321

you can do achieve it like below :

SELECT ID, FROM_UNIXTIME(UNIX_TIMESTAMP(time, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"),"yyyy-MM-dd") as time
FROM yourtable

Upvotes: 1

Standin.Wolf
Standin.Wolf

Reputation: 1234

Just use the substring function for this

select substr(time,1,10) as date from tablename;

Update This would convert it back to date and you can do your sorting

select cast(substr(time,1,10) as date) as date from tablename;

Upvotes: 3

Related Questions