September 15, 2010 at 8:34 am
I had previously asked for advice for an ORDER BY on a 24-hour time column and the suggestion that worked was this:
ORDER BY CONVERT(VARCHAR, D.pageTime, 108)
I now have a date field like this 1/1/2010, that needs sorting. So I figured this would work:
ORDER BY CONVERT(VARCHAR, D.pageDate, 103)
But it doesn't work at all, it just sorts the dates seemingly randomly.
Why would the above method work for times but not dates?
Thanks!
September 15, 2010 at 8:39 am
If you look in BoL, you'll see that format code 103 formats the date dd/mm/yyyy, so it'll be sorted in that order, by day first, then month, then year. I suspect what you want is yyyy-mm-dd. In that case, you don't need a convert at all, just order by the date column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2010 at 8:51 am
BOL Reference: http://msdn.microsoft.com/en-us/library/ms187928%28v=SQL.100%29.aspx
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 15, 2010 at 8:58 am
Oh I see...thanks for BOL link.
I tried using 101 instead of 103, and I still get a random order by.
I also just tried doing ORDER BY pageDate, but that generated an error:
"Conversion failed when converting date and/or time from character string"
September 15, 2010 at 9:02 am
Magy (9/15/2010)
Oh I see...thanks for BOL link.I tried using 101 instead of 103, and I still get a random order by.
101 orders Month/day/year, I don't think that's what you want either.
It's not random, but I doubt it's what you want, to have 2 Jan 2010 coming before 5 Jan 2009
I also just tried doing ORDER BY pageDate, but that generated an error:
Shouldn't. Post query, table structure and some sample data
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2010 at 9:04 am
Are these dates stored as strings? Can you show some data or results?
ORDER BY doesn't do a random order. There must be a pattern if you have used it.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 15, 2010 at 9:14 am
dates are stored in datetime format:
2010-04-28 00:00:00.000
2010-04-29 00:00:00.000
2010-04-29 00:00:00.000
2010-04-29 00:00:00.000
2010-04-30 00:00:00.000
2010-05-03 00:00:00.000
2010-05-03 00:00:00.000
2010-05-04 00:00:00.000
2010-05-04 00:00:00.000
2010-05-05 00:00:00.000
2010-05-05 00:00:00.000
2010-05-06 00:00:00.000
2010-05-07 00:00:00.000
2010-05-10 00:00:00.000
September 15, 2010 at 9:18 am
Table definition and query please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2010 at 9:29 am
Magy (9/15/2010)
Oh I see...thanks for BOL link.I tried using 101 instead of 103, and I still get a random order by.
I also just tried doing ORDER BY pageDate, but that generated an error:
"Conversion failed when converting date and/or time from character string"
101 and 103 are BOTH poor choices if you want it in a logical date order.
Consider the following dates:
January 10, 2010
March 3, 2009
June 20, 2000
Now if you write them as DD/MM/YYYY and sort them, you get the following order
03/03/2009
10/01/2010
20/06/2000
Which may seem random to you.
You tried to fix that by going MM/DD/YYYY, but that would give
01/10/2010
03/03/2009
06/20/2000
Which would look like descending order when you wanted ascending.
So sort by a string representing a date, you need the year represented first, then the month, then the day. Because every day in a later year should come after the last day in a prior year, and ditto with months. It compares one character at a time.
Or, as Gail said, you ought to be able to just sort by the date without converting it.
September 15, 2010 at 9:42 am
Magy (9/15/2010)
I also just tried doing ORDER BY pageDate, but that generated an error:"Conversion failed when converting date and/or time from character string"
Are you sure that pageDate is a DateTime datatype? It's sounding like it's a character column.
Please post the table structure as a CREATE TABLE statement, some sample data to put into the table as INSERT statements, and the query you're using - see the first link in my signature for how to do so.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 9:55 am
Here is my table and query:
/****** Object: Table [dbo].[pageList] Script Date: 09/15/2010 11:44:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pageList](
[userID] [int] NULL,
[pageName] [nvarchar](255) NULL,
[pageDate] [datetime] NULL,
[ownerID] [varchar](50) NULL,
[showEndDate] [int] NULL
SET ANSI_PADDING OFF
GO
INSERT INTO pageList (1, test1,getdate() -1, 2001, 1)
INSERT INTO pageList (2, test1,getdate() -3, 2002, 1)
INSERT INTO pageList (3, test1,getdate() -6, 2003, 1)
INSERT INTO pageList (4, test1,getdate() -12, 2004, 1)
INSERT INTO pageList (5, test1,getdate() -24, 2005, 1)
INSERT INTO pageList (6, test1,getdate() -48, 2006, 1)
SELECT userID, CONVERT(varchar, pageDate, 101) AS 'Page Date' from pageList
ORDER BY CONVERT(varchar, pageDate, 101)
Hope that helps,
Thanks!
September 15, 2010 at 10:03 am
I'm guessing you didn't test that, as neither the create table nor the inserts worked.
I fixed it for you this time:
CREATE TABLE [dbo].[pageList] (
[userID] [int] NULL,
[pageName] [nvarchar](255) NULL,
[pageDate] [datetime] NULL,
[ownerID] [varchar](50) NULL,
[showEndDate] [int] NULL
);
GO
INSERT INTO pageList VALUES (1, 'test1',getdate()-1, 2001, 1);
INSERT INTO pageList VALUES (2, 'test1',getdate()-3, 2002, 1);
INSERT INTO pageList VALUES (3, 'test1',getdate()-6, 2003, 1);
INSERT INTO pageList VALUES (4, 'test1',getdate()-12, 2004, 1);
INSERT INTO pageList VALUES (5, 'test1',getdate()-24, 2005, 1);
INSERT INTO pageList VALUES (6, 'test1',getdate()-48, 2006, 1);
And this works fine to return the data ordered by date.
SELECT userID, CONVERT(varchar(10), pageDate, 101) AS 'Page Date' from pageList
ORDER BY pageDate
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 15, 2010 at 10:09 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE #pagelist(
[userID] [int] NULL,
[pageName] [nvarchar](255) NULL,
[pageDate] [datetime] NULL,
[ownerID] [varchar](50) NULL,
[showEndDate] [int] NULL)
SET ANSI_PADDING OFF
GO
INSERT INTO #pagelist (userID,pageName,pageDate,ownerID,showEndDate) values (1, 'test1',getdate() -1, '2001', 1)
INSERT INTO #pagelist (userID,pageName,pageDate,ownerID,showEndDate) values (2, 'test1',getdate() -3, '2002', 1)
INSERT INTO #pagelist (userID,pageName,pageDate,ownerID,showEndDate) values (3, 'test1',getdate() -6, '2003', 1)
INSERT INTO #pagelist (userID,pageName,pageDate,ownerID,showEndDate) values (4, 'test1',getdate() -12, '2004', 1)
INSERT INTO #pagelist (userID,pageName,pageDate,ownerID,showEndDate) values (5, 'test1',getdate() -24, '2005', 1)
INSERT INTO #pagelist (userID,pageName,pageDate,ownerID,showEndDate) values (6, 'test1',getdate() -48, '2006', 1)
SELECT userID, CONVERT(varchar, pageDate, 101) AS 'Page Date' from #pagelist
ORDER BY pageDate
drop table #pagelist
This works fine for me. We explained above why ordering by 101 or 103 is a bad idea.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply