SQL query Date Sort Problem
-
Thanks for your reply. In SQL server, regional setting is set as default. Moreover, I need to do sorting based on Date only otherwise, it will defeat my purpose. However, as you are saying, i will try to do so.
I got a gut-feeling saying it's converting to
DATEto aVARCHAR, that's the only way to have 10 precede the number 2. Contrary to the previous advice, what happens when you cast it to aDOUBLE? Internally, a date is a floating-point number.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Please note I did not offer ANY help on that subject - it does not make any sense to me so I concentrated on the other areas hoping they may have an impact on your results. To me a query cannot return inconsistent results!
Never underestimate the power of human stupidity RAH
Yes, that's my core question. even if my table design is not good, even if SQL may internally converting date into float or even if i need to put 'ASC' after Order By, still question remains same. Why on earth SQL query is not consistent? Why not it's producing same results 100 out of 100 times? Is that means any internal bug in the SQL Server? Thanks.
-
Please note I did not offer ANY help on that subject - it does not make any sense to me so I concentrated on the other areas hoping they may have an impact on your results. To me a query cannot return inconsistent results!
Never underestimate the power of human stupidity RAH
Actually, based on the query results, within a loop creating new records and putting into a new SQL table 'Timesheet'. Now, when I see this new table 'Timesheet', it's rows are not created based on that sorting. For example, output of the query I got as 1/1/2012 to 1/31/2012 and now in the new table, inserting records like : 1/1/2012 - 51 - 12 1/2/2012 - 10 - 13 1/3/2012 - 15 - 20 ..... till 1/31/2012 - 40 - 30 Now, sometimes (as I said earlier), it's creating rows in 'Timesheet' table that looks like: 1/12/2012 - 10 -20 1/13/2012 - 15-12 .. then 1/31/2012 - 40 - 30 ..then 1/1/2012 - 51 - 12 1/2/2012 - 10 - 13 1/3/2012 - 15 - 20 It's not that all errors are starting from 1/12/2012, it can be 10th or 15th. So no possible pattern in these output which are giving error. May be it will give more light into this matter. thanks.
-
Actually, based on the query results, within a loop creating new records and putting into a new SQL table 'Timesheet'. Now, when I see this new table 'Timesheet', it's rows are not created based on that sorting. For example, output of the query I got as 1/1/2012 to 1/31/2012 and now in the new table, inserting records like : 1/1/2012 - 51 - 12 1/2/2012 - 10 - 13 1/3/2012 - 15 - 20 ..... till 1/31/2012 - 40 - 30 Now, sometimes (as I said earlier), it's creating rows in 'Timesheet' table that looks like: 1/12/2012 - 10 -20 1/13/2012 - 15-12 .. then 1/31/2012 - 40 - 30 ..then 1/1/2012 - 51 - 12 1/2/2012 - 10 - 13 1/3/2012 - 15 - 20 It's not that all errors are starting from 1/12/2012, it can be 10th or 15th. So no possible pattern in these output which are giving error. May be it will give more light into this matter. thanks.
..and the table "timesheet" holds the day as a
VARCHAR? That's not a bug, that's by design. Strings are sorted depending on the settings, should have beenDATEtype.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
..and the table "timesheet" holds the day as a
VARCHAR? That's not a bug, that's by design. Strings are sorted depending on the settings, should have beenDATEtype.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
No sir, the sorting is made on 'ToDate' column which is of Date type, not varchar type, as I already given my column types before.
-
No sir, the sorting is made on 'ToDate' column which is of Date type, not varchar type, as I already given my column types before.
AnirbanM 2 wrote:
No sir, the sorting is made on 'ToDate' column which is of Date type, not varchar type, as I already given my column types before.
Still, it sorts like it would be. Can you create a TSQL-script that reproduces the problem? Seems I can't, my dates keep returning ordered in a way that could be expected;
DECLARE @TEST TABLE (SD DATE);
INSERT INTO @TEST (SD) VALUES ('20010101');
INSERT INTO @TEST (SD) VALUES ('20011010');
INSERT INTO @TEST (SD) VALUES ('20010310');
INSERT INTO @TEST (SD) VALUES ('20010210');
INSERT INTO @TEST (SD) VALUES ('20010301');
INSERT INTO @TEST (SD) VALUES ('20010201');
SELECT * FROM @TEST ORDER BY SD;Results:
2001-01-01
2001-02-01
2001-02-10
2001-03-01
2001-03-10
2001-10-10Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
I have a simple SQL table as follows:
CREATE TABLE [dbo].[DaysMonth](
[SNo] [int] NOT NULL,
[Month] [varchar](20) NOT NULL,
[Year] [varchar](4) NOT NULL,
[NoOfDays] [int] NOT NULL,
[Days] [nchar](10) NULL,
[Holiday] [nchar](1) NULL,
[ToDate] [date] NULL,
[Period] [nchar](30) NULL,Following is the query:
Select SNo, NoOfDays, [Month], [Year], [Days], [Holiday] From DaysMonth Where [Period]='November,2012' order by ToDate
Now, i'm using this query in web application using ASP.Net environment and C# language within a loop. using SQL Server 2008. basic purpose here is to get all days within a period, say January,2012 in a sorted manner. Out of 100 times running this query, 97-98 times, it's producing correct result, however, 1-2 times, sort is not working, so instead of 1st to 31st, output coming like 10th to 31st then 1st to 10th. Getting puzzled, as it's a huge application running on multi-user environment, getting very difficult to take care of error which is absolutely unpredictable and illogical. Please suggest where is the problem, thanks to all in advance.
I would think the error is happening after the query is returned. Test the query in SQL alone and see if you get the error you describe.
-
I have a simple SQL table as follows:
CREATE TABLE [dbo].[DaysMonth](
[SNo] [int] NOT NULL,
[Month] [varchar](20) NOT NULL,
[Year] [varchar](4) NOT NULL,
[NoOfDays] [int] NOT NULL,
[Days] [nchar](10) NULL,
[Holiday] [nchar](1) NULL,
[ToDate] [date] NULL,
[Period] [nchar](30) NULL,Following is the query:
Select SNo, NoOfDays, [Month], [Year], [Days], [Holiday] From DaysMonth Where [Period]='November,2012' order by ToDate
Now, i'm using this query in web application using ASP.Net environment and C# language within a loop. using SQL Server 2008. basic purpose here is to get all days within a period, say January,2012 in a sorted manner. Out of 100 times running this query, 97-98 times, it's producing correct result, however, 1-2 times, sort is not working, so instead of 1st to 31st, output coming like 10th to 31st then 1st to 10th. Getting puzzled, as it's a huge application running on multi-user environment, getting very difficult to take care of error which is absolutely unpredictable and illogical. Please suggest where is the problem, thanks to all in advance.
AnirbanM 2 wrote:
Please suggest where is the problem, thanks to all in advance.
Some posssible reasons. - The problem originates from some other place in the code. So nothing to do with the code that you posted. And one very obvious possibility is that there is code somewhere that is sorting based on alphabetic ("10", "11" versus "1", "2) instead of using the sort that you gave. - You are not looking at the actual data. - You are not running the code that you think you are.