1

I have an issue as I am trying to select Date values stored in SQL server as String value with this format "Thu, 08 Jul 2021 06:08:20 -0700" and i need to select all table with newest date in first but I do not know how to convert this String into Date and sort it. Thanks in advance.

Table

 |Thu, 08 Jul 2021 06:08:20 -0700| |Fri, 09 Jul 2021 01:08:20 -0700| |Sun, 11 Jul 2021 07:08:20 -0700|

output (Newest Date first)

 |Sun, 11 Jul 2021 07:08:20 -0700| |Fri, 09 Jul 2021 01:08:20 -0700| |Thu, 08 Jul 2021 06:08:20 -0700|
Improve this question
4
  • I couldn't even parsing working with TRY_CONVERT. You should stop storing your dates as strings. Commented Jul 19, 2021 at 23:48
  • i will try to sort this values as a date but i also do not know if i can insert this format with convert or without convert and if i need to convert i do not know the format Commented Jul 19, 2021 at 23:51
  • You can't use CONVERT as far as I know. Commented Jul 19, 2021 at 23:52
  • okay thanks for your support and you comment and i will wait hopping that some one face this issue before and can solve it :) Commented Jul 19, 2021 at 23:55

1 Answer 1

Reset to default
2

Your date is just missing a valid timezone offset value so needs a ":" inserted so it's -07:00, you can do this with stuff and use substring to ignore the irrelevant day name. You don't state a specific database platform, for SQL Server you can then cast to a datetimeoffset, other databases have similar but slightly varied syntax. This assumes the strings are all formatted consistently of course.

declare @d varchar(30)='Thu, 08 Jul 2021 06:08:20 -0700' select Cast(Stuff(Substring(@d,6,26),25,0,':') as datetimeoffset(0))

Result

2021-07-08 06:08:20 -07:00

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your response but i need to know how to select the whole table and sort the result with the date sorted with this format "Thu, 08 Jul 2021 06:08:20 -0700"
Ok so ideally you would store the data as an actual date data type, however you can simply apply the cast in the order by - so for example select * from table order by Cast(Stuff(Substring(DateColumnName,6,26),25,0,':') as datetimeoffset(0))

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.