sorting date in sql
I have dates stored in my column as
Wednesday, November 21, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 15, 2018
Tuesday, November 27, 2018
Tuesday, November 06, 2018
Monday, November 19, 2018
I am using
ORDER BY CONVERT(varchar(100), submissionDate, 101) DESC
but its not giving me column in sorted way. My column is NVARCHAR(MAX)
sql
sql-server tsql edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
|
show 2 more comments
I have dates stored in my column as
Wednesday, November 21, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 15, 2018
Tuesday, November 27, 2018
Tuesday, November 06, 2018
Monday, November 19, 2018
I am using
ORDER BY CONVERT(varchar(100), submissionDate, 101) DESC
but its not giving me column in sorted way. My column is NVARCHAR(MAX)
sql
sql-server tsql edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
3
is it varchar??? you should have stored it as DATE
– nikhil sugandh
Nov 28 '18 at 9:37
Convert formatted dates to dates. Then format them on demand.
– Salman A
Nov 28 '18 at 9:41
Which dbms are you using? (That convert code is product specific.)
– jarlh
Nov 28 '18 at 9:41
Why are you converting in the ORDER BY instead of using the submissionDate directly? What sort order do you want (varchar sorts alphabetically)?
– Hans Kesting
Nov 28 '18 at 9:44
3
You probably don't what to hear this: if possible, you should definitely change the type of that column to date or datetime. Nvarchar (even worse, max) is a terrible format to store dates.
– johey
Nov 28 '18 at 9:54
|
show 2 more comments
I have dates stored in my column as
Wednesday, November 21, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 15, 2018
Tuesday, November 27, 2018
Tuesday, November 06, 2018
Monday, November 19, 2018
I am using
ORDER BY CONVERT(varchar(100), submissionDate, 101) DESC
but its not giving me column in sorted way. My column is NVARCHAR(MAX)
sql
sql-server tsql edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
I have dates stored in my column as
Wednesday, November 21, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 22, 2018
Wednesday, August 15, 2018
Tuesday, November 27, 2018
Tuesday, November 06, 2018
Monday, November 19, 2018
I am using
ORDER BY CONVERT(varchar(100), submissionDate, 101) DESC
but its not giving me column in sorted way. My column is NVARCHAR(MAX)
sql
sql-server tsql sql
sql-server tsql edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
edited Nov 28 '18 at 10:47
a_horse_with_no_name
304k46465561
304k46465561
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
asked Nov 28 '18 at 9:36
Mohammad ShahbazMohammad Shahbaz
12712
12712
3
is it varchar??? you should have stored it as DATE
– nikhil sugandh
Nov 28 '18 at 9:37
Convert formatted dates to dates. Then format them on demand.
– Salman A
Nov 28 '18 at 9:41
Which dbms are you using? (That convert code is product specific.)
– jarlh
Nov 28 '18 at 9:41
Why are you converting in the ORDER BY instead of using the submissionDate directly? What sort order do you want (varchar sorts alphabetically)?
– Hans Kesting
Nov 28 '18 at 9:44
3
You probably don't what to hear this: if possible, you should definitely change the type of that column to date or datetime. Nvarchar (even worse, max) is a terrible format to store dates.
– johey
Nov 28 '18 at 9:54
|
show 2 more comments
3
is it varchar??? you should have stored it as DATE
– nikhil sugandh
Nov 28 '18 at 9:37
Convert formatted dates to dates. Then format them on demand.
– Salman A
Nov 28 '18 at 9:41
Which dbms are you using? (That convert code is product specific.)
– jarlh
Nov 28 '18 at 9:41
Why are you converting in the ORDER BY instead of using the submissionDate directly? What sort order do you want (varchar sorts alphabetically)?
– Hans Kesting
Nov 28 '18 at 9:44
3
You probably don't what to hear this: if possible, you should definitely change the type of that column to date or datetime. Nvarchar (even worse, max) is a terrible format to store dates.
– johey
Nov 28 '18 at 9:54
3
3
is it varchar??? you should have stored it as DATE
– nikhil sugandh
Nov 28 '18 at 9:37
is it varchar??? you should have stored it as DATE
– nikhil sugandh
Nov 28 '18 at 9:37
Convert formatted dates to dates. Then format them on demand.
– Salman A
Nov 28 '18 at 9:41
Convert formatted dates to dates. Then format them on demand.
– Salman A
Nov 28 '18 at 9:41
Which dbms are you using? (That convert code is product specific.)
– jarlh
Nov 28 '18 at 9:41
Which dbms are you using? (That convert code is product specific.)
– jarlh
Nov 28 '18 at 9:41
Why are you converting in the ORDER BY instead of using the submissionDate directly? What sort order do you want (varchar sorts alphabetically)?
– Hans Kesting
Nov 28 '18 at 9:44
Why are you converting in the ORDER BY instead of using the submissionDate directly? What sort order do you want (varchar sorts alphabetically)?
– Hans Kesting
Nov 28 '18 at 9:44
3
3
You probably don't what to hear this: if possible, you should definitely change the type of that column to date or datetime. Nvarchar (even worse, max) is a terrible format to store dates.
– johey
Nov 28 '18 at 9:54
You probably don't what to hear this: if possible, you should definitely change the type of that column to date or datetime. Nvarchar (even worse, max) is a terrible format to store dates.
– johey
Nov 28 '18 at 9:54
|
show 2 more comments
2 Answers
2
active
oldest
votes
If you are stuck with the column type nvarchar, you can convert the value to a date with something like this:
SELECT *, submissionDate
, convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107) as my_date
FROM your_table
ORDER BY my_date
Or just
SELECT submissionDate
FROM your_table
ORDER BY convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107)
The conversion is a bit cumbersome because of your date format. Sql Server does not seem to have a matching built-in format. So we get rid of the day of the week (Monday,..) by removing the part before the first comma and then the result complies with format 107 (Mon dd, yyyy).
answered Nov 28 '18 at 10:14
joheyjohey
388316
add a comment |
SQL Server is pretty good about recognizing arcane formats as dates, but not good enough for this situation. I think that stuff() makes this simpler:
select convert(date,
stuff(datestr, 1, charindex(',', datestr) + 1, '')
)
from (values ('Wednesday, November 21, 2018')) v(datestr)
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53516292%2fsorting-date-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you are stuck with the column type nvarchar, you can convert the value to a date with something like this:
SELECT *, submissionDate
, convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107) as my_date
FROM your_table
ORDER BY my_date
Or just
SELECT submissionDate
FROM your_table
ORDER BY convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107)
The conversion is a bit cumbersome because of your date format. Sql Server does not seem to have a matching built-in format. So we get rid of the day of the week (Monday,..) by removing the part before the first comma and then the result complies with format 107 (Mon dd, yyyy).
answered Nov 28 '18 at 10:14
joheyjohey
388316
add a comment |
If you are stuck with the column type nvarchar, you can convert the value to a date with something like this:
SELECT *, submissionDate
, convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107) as my_date
FROM your_table
ORDER BY my_date
Or just
SELECT submissionDate
FROM your_table
ORDER BY convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107)
The conversion is a bit cumbersome because of your date format. Sql Server does not seem to have a matching built-in format. So we get rid of the day of the week (Monday,..) by removing the part before the first comma and then the result complies with format 107 (Mon dd, yyyy).
answered Nov 28 '18 at 10:14
joheyjohey
388316
add a comment |
If you are stuck with the column type nvarchar, you can convert the value to a date with something like this:
SELECT *, submissionDate
, convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107) as my_date
FROM your_table
ORDER BY my_date
Or just
SELECT submissionDate
FROM your_table
ORDER BY convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107)
The conversion is a bit cumbersome because of your date format. Sql Server does not seem to have a matching built-in format. So we get rid of the day of the week (Monday,..) by removing the part before the first comma and then the result complies with format 107 (Mon dd, yyyy).
answered Nov 28 '18 at 10:14
joheyjohey
388316
If you are stuck with the column type nvarchar, you can convert the value to a date with something like this:
SELECT *, submissionDate
, convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107) as my_date
FROM your_table
ORDER BY my_date
Or just
SELECT submissionDate
FROM your_table
ORDER BY convert(date
, right(submissionDate, len(submissionDate)
- charindex(',', submissionDate))
, 107)
The conversion is a bit cumbersome because of your date format. Sql Server does not seem to have a matching built-in format. So we get rid of the day of the week (Monday,..) by removing the part before the first comma and then the result complies with format 107 (Mon dd, yyyy).
answered Nov 28 '18 at 10:14
joheyjohey
388316
answered Nov 28 '18 at 10:14
joheyjohey
388316
answered Nov 28 '18 at 10:14
joheyjohey
388316
answered Nov 28 '18 at 10:14
joheyjohey
388316
388316
add a comment |
add a comment |
SQL Server is pretty good about recognizing arcane formats as dates, but not good enough for this situation. I think that stuff() makes this simpler:
select convert(date,
stuff(datestr, 1, charindex(',', datestr) + 1, '')
)
from (values ('Wednesday, November 21, 2018')) v(datestr)
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
add a comment |
SQL Server is pretty good about recognizing arcane formats as dates, but not good enough for this situation. I think that stuff() makes this simpler:
select convert(date,
stuff(datestr, 1, charindex(',', datestr) + 1, '')
)
from (values ('Wednesday, November 21, 2018')) v(datestr)
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
add a comment |
SQL Server is pretty good about recognizing arcane formats as dates, but not good enough for this situation. I think that stuff() makes this simpler:
select convert(date,
stuff(datestr, 1, charindex(',', datestr) + 1, '')
)
from (values ('Wednesday, November 21, 2018')) v(datestr)
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
SQL Server is pretty good about recognizing arcane formats as dates, but not good enough for this situation. I think that stuff() makes this simpler:
select convert(date,
stuff(datestr, 1, charindex(',', datestr) + 1, '')
)
from (values ('Wednesday, November 21, 2018')) v(datestr)
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
answered Nov 28 '18 at 12:14
Gordon LinoffGordon Linoff
788k35313418
788k35313418
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53516292%2fsorting-date-in-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
is it varchar??? you should have stored it as DATE
– nikhil sugandh
Nov 28 '18 at 9:37
Convert formatted dates to dates. Then format them on demand.
– Salman A
Nov 28 '18 at 9:41
Which dbms are you using? (That convert code is product specific.)
– jarlh
Nov 28 '18 at 9:41
Why are you converting in the ORDER BY instead of using the submissionDate directly? What sort order do you want (varchar sorts alphabetically)?
– Hans Kesting
Nov 28 '18 at 9:44
3
You probably don't what to hear this: if possible, you should definitely change the type of that column to date or datetime. Nvarchar (even worse, max) is a terrible format to store dates.
– johey
Nov 28 '18 at 9:54