Order By Descending Date
Feb 1, 2007
Here is my sp. I want it to order by descending date, but now it is 1st Feb it is putting this at the bottom, though January is still sorted fine. I need the dates to display in UK format dd/mm/yy
CREATE Procedure [dbo].[spRMU_CountNoDailyUsers]
AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , Log_Username
ORDER BY CONVERT(varchar, Log_DateTime, 103) desc, No_Logins desc
GO
View 2 Replies
ADVERTISEMENT
Jun 18, 2014
Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...
IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),
[Code] ....
View 6 Replies
View Related
Sep 21, 2007
Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.
How do I keep it in DESC order for viewing? Here's the statement:
SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC
View 3 Replies
View Related
May 21, 2014
I am trying to sort this simplied table:
ID - Time
1 2000-2001
2 2002-
3 2001-2003
4 1999
5 2005-2006
I want this as a result:
1999
2000-2001
2001-2003
2005-2006
2002-
Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...
Now, simply doing a
SELECT * FROM [table] ORDER BY TIME;
Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.
So, of course, it fails on all entries with a leading "-" ...
Right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...
Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it...
View 5 Replies
View Related
May 8, 2007
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez
View 4 Replies
View Related
Jul 23, 2005
I have a unique index based on the following columns:ProjectID (int)MaterialCatalogID (int)Material catalogues are pretty much static but projects are dynamic andpeople are most likely to be working on the latest project so wouldusing a descending sort on the ProjectID in the index gain anyperformance?
View 2 Replies
View Related
Oct 8, 2013
I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.
Example:
Source column:
2008
2005
2010
2013
All-Year
Month-year
Required Output:
Source Column:
2013
2010
2008
2005
Month-Year
All-year
View 4 Replies
View Related
Jun 24, 2008
can any one help me to sort a table on the basis of one colomn without using ORDER BY Desc.
Rahul Arora
07 Batch
NCCE Israna,
######################
IMPOSSIBLE = I+M+POSSIBLE
View 6 Replies
View Related
Jun 18, 2007
I have been using Access 2003 as a front end to a SQL 2k database. Dates are sorted in a descending fashion. I recently upgraded the sql server database to 2005 and dates can no longer be sorted properly. I have set the database compatibility level to sql2000 within the sql2005 database engine but no luck. Any suggetions on how to fix this?
Thanks in advance.
View 1 Replies
View Related
Oct 10, 2007
Hi,
I have this statement:
SELECT DATENAME(month, date) AS Month, COUNT(*) AS Total
FROM Table
GROUP BY DATENAME(month, date)
I want to order it in month order...
If i order it by DATENAME(month, date) then it is just in alphabetical.
Also is there anyway that all months are added to the result even if there are no records for that month (So it will just show 0 in the count)
Thanks for anyhelp
View 7 Replies
View Related
Apr 26, 2007
Hi!I have a little problem. I’m trying to sort a date I have converted like thisConvert(datetime,LH.LoginDateTime,103) as RegistrationDateBut when I use Order by on RegistrationDate it only sort on days:01/11/200601/12/200602/11/200602/12/200603/11/200603/12/2006I’ll guess it’s because of the “varchar� convert, but I need the date to bee inn this format, since I only shall check the date and not the time. Is there a way around this, so I can order it like this? (Se under)01/11/200602/11/200603/11/200601/12/200602/12/200603/12/2006Sample SQL;select Convert(varchar,LH.LoginDateTime,103) as RegistrationDate,select count(*) from LoginHistory AS LH2 where datepart(hh,LH2.LoginDateTime)<7 ANDConvert(varchar,LH2.LoginDateTime,103)>=Convert(varchar,LH.LoginDateTime,103) AND Convert(varchar,LH2.LoginDateTime,103)<=Convert(varchar,LH.LoginDateTime,103)) As beforehour07from LoginHistory AS LHwhere LH.LoginDateTime >='''+ Convert(varchar,@FromDate,113) + ''' ' + 'and LH.LoginDateTime <='''+ Convert(varchar,@ToDate,113) + ''' ' + 'group by Convert(varchar,LH.LoginDateTime,103)'Order by RegistrationDate
View 4 Replies
View Related
Feb 3, 2008
Hello there,I have a problem when I'm trying to order by the date..I have tried this string:SelectCommand="SELECT TOP 5 [Date], [Id], [Navn], [ShortInfo] FROM [fest] ORDER BY [Date] DESC">
And I have these dates:
04/02/2008
06/02/2008
20/02/2008
29/02/2008
08/03/2008
28/03/2008They should be shown like this:
04/02/2008
06/02/2008
20/02/2008
29/02/2008
08/03/2008
But they dosn't, insted is they shown like this:
29/02/200828/03/200820/02/200808/03/200806/02/2008How can I do, so it work?Regards Jeppe Richardt
View 8 Replies
View Related
Jul 20, 2005
Hi,I have a table (SQL Server 2000) with several date columns in it, all ofwhich are individually NULLable, but in any one row, not all the dates canbe NULL.I want a query which ORDERs BY the earliest date it finds in each row. I'mguessing I have to do this in two steps:STEP 1Using a UDF, find the earliest date and stick it in a new calculatedcolumn "earliest date"STEP 2ORDER BY this UDF-created columnIf this is the right way to go about this, is there a simple SQL way ofdetermining which is the lowest of several dates? (ie of doing STEP 1).Or am I looking at this the wrong way, and missing an easy *one-step* way ofgetting what I want?TIA,JON
View 5 Replies
View Related
Oct 12, 2006
SELECT
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' +
RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,
SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,
SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,
SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,
SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,
SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,
SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as Others
From
task_table a,act_table b where a.status_id=b.act_id and
a.user_id=(select user_id from user_table where user_name='Raghu') and
a.task_date like '%/%/2006'
GROUP BY
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)
Output :
Aug 2006 294 0 0 80 0 0
Jan 2006 14 0 0 0 0 0
Oct 2006 336 0 0 0 0 0
Sep 2006 3262 20 24 8 16 0
How to sort the date in ascending Order ?
Jan 2006
Aug 2006
Sep 2006
Oct 2006
View 5 Replies
View Related
Sep 21, 2006
hi,I was pulling up a report in SQL, and I wanted the records to be ordered by dates descending. However, I found this ordering was only fine enough to order records by dates (not hours or minutes) (within the same date, records were ordered so that the latest entered were at the bottom). I wonder if anyone else has encouted this problem before, or I am doing something wrong.Thanks very much.
View 2 Replies
View Related
Jan 18, 2005
I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB.
I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM
Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this:
1/18/2005 1:03:19 AM
get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11.
Anyone know what I can do to get this sorted correctly?
View 3 Replies
View Related
Oct 3, 2006
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2
oct 3
sep 13
sep 21
sep 22
sep 30
aug 3
aug 5
aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
View 7 Replies
View Related
Jun 2, 2008
Hi all,
I have 2 tables, Order and Payment
Order
Order_NumberCust_NumberOrder_Date
10001 C1 23-May
10002 C2 24-May
10003 C1 25-May
10004 C3 28-May
Payment
Order_Number|Card_Type|Card_#|Merchant
10001 Gift Card1234null
10001 Gift Card1235null
10001 Gift Card 1236null
10001 Credit Cardxxxxprd
10002 Credit Cardxxxxprd
10003 Credit Card xxxxprd
10004 Credit Card xxxx prod
I have to populate th below table to track last gift card used for each cust_number.
1. last gift card used for each customer, each order
2. In a single order , if card used is gift card, last gift card used is gift card itself. if card used is a credit card, then the max gift card number from with in the order.
3. First time if a customer uses a credit card, then last gift card used is defaulted to 99 for merchant = prd and 88 for merchant = prod
4. In a new order, a past customer only uses a gift card, then last gift card used is gift card from his previous order.
[b]Last_Gift_Card
Cust_No|Order_number|card_number|last_gift_card
C11000112431234
C11000112351235
C11000112361236
C110001xxxx1236
C210002xxxx99
C110003xxxx1236
C310004xxxx88
Please help me with the sql.
I tried this using subqueries to find the max gift card for a customer for an order and could get the last gift card used correctly for credit cards for an order, but not able to insert the gift card from a previous order if the new order has only credit card as in for customer C1.
Thanks.
View 6 Replies
View Related
Jan 16, 2015
I have below SQL, which should be order by posteddate ASC
SELECT AnnouncementID,[Subject],[Description],
CONVERT(nvarchar(10),PostedDate,101) AS PostedDate,
CONVERT(nvarchar(10),ExpiredDate,101) AS ExpiredDate,
CountryID,CreatedBy, CreatedDate, ModifiedBy, ModifiedDate
FROM Announcements a
WHERE isActive = 1
AND CountryID = 2
AND (GETDATE()>= PostedDate)
AND (GETDATE()<= ExpiredDate)
ORDER BY PostedDate ASC
But result is displaying as below, PostedDate datatype is datetime
01/01/2015
01/02/2015
12/28/2014
12/31/2014
Expected result is
01/02/2015
01/01/2015
12/31/2014
12/28/2014
View 1 Replies
View Related
Jul 20, 2005
How do I order a query by a date field ASC, but have any NULL valuesshow up last? i.e.7/1/20037/5/20037/10/2003<NULL><NULL>Any help will greatly be appreciated
View 3 Replies
View Related
Aug 2, 2007
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>"
SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource>
<asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1">
<HeaderTemplate>
<ul>
</HeaderTemplate>
<ItemTemplate>
<li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.
View 2 Replies
View Related
Mar 4, 2004
Hi everyone.
I know, I know, it should have been a datetime from the start...but here's the problem.
I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:
ORDER BY [Date]
Are there any work arounds for this? Is there some way of doing:
ORDER BY covert(datetime, [Date], 103) or something?
Cheers
Andrew
View 3 Replies
View Related
Feb 3, 2006
When clicking on the header for the Date Created column in EM (SQL Server 2000) , in the tables list for a database, I noticed that the tables don't get sorted by created date at all - looks like random order, without looking too close.
Is this is a bug (probably not??) or some kind of collation-related problem?
View 2 Replies
View Related
May 23, 2008
how do you get it to sort correctly for the date? at the minute its sorting on the characters rather than actual date value
Code Snippet
INSERT INTO @TempItems (OrderID)
SELECT OrderID
FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
ORDER BY
CASE @SortOrder --Order ASC
WHEN 0 THEN cast(OrderID as varchar(100))
WHEN 1 THEN cast(c.CompanyName as varchar(100))
WHEN 2 THEN cast(e.FirstName as varchar(100))
WHEN 3 THEN cast(o.OrderDate as varchar(100))
WHEN 4 THEN cast(o.RequiredDate as varchar(100))
WHEN 5 THEN cast(o.ShippedDate as varchar(100))
ELSE '1'
END,
View 4 Replies
View Related
Sep 29, 2015
I have a report that displays its date dropdown badly: 10AM,10PM, 11AM, 11PM etc when it should be 10AM, 11AM, etc. The used measure is Hour Name which looks like "01AM" "02AM" etc. And here is the current query
WITHÂ
MEMBER [Measures].[ParameterCaption] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.MEMBER_CAPTIONÂ
MEMBER [Measures].[ParameterValue] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Report Verified Time].[Hour Name].CURRENTMEMBER.LEVEL.ORDINALÂ
SELECT {[Measures].[ParameterCaption],
[Measures].[ParameterValue],Â
[Code] ....
View 2 Replies
View Related
Apr 10, 2014
I have files which has date in file name and I want to load all files in sequential order.
Here I am giving you an example of my file name.
"NetworkActivity_869_403722_01-01-2014.log"
"NetworkActivity_869_403722_01-02-2014.log"
"NetworkActivity_869_403722_01-03-2014.log"
"NetworkActivity_869_403722_01-04-2014.log"
"NetworkActivity_869_403722_01-05-2014.log"
These are my files in that I want to load files sequentially means jan 1st, 2nd like this way.
View 5 Replies
View Related
Jun 16, 2014
I am working on a query that needs to return the record order number with the most recent requested delivery date.
It seems to work most of the time, but I have found some glitches for some of the items.
example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014
I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014
This query is what I have so far:
SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'
[code]....
WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?
View 9 Replies
View Related
Feb 1, 2007
I am using Access 2003 as a front-end to a SQL Server 2005 database.I make design changes using SQL Server Management Studio. I have atable that includes a datetime column. I create a view and sort bythe datetime field. When I initially look at the result it is sortedcorrectly. Then I save the view and re-open it and it is not sorted.I've simplified the view so it only contains the date field and itstill does not sort. Here is the view:SELECT TOP (100) PERCENT Period_DateFROM dbo.Period_SummaryORDER BY Period_Date DESCThe date seems to be a random order.I don't have this problem in the SQL Server 2000 version of thedatabase.Help please!Thanks,Jerry
View 4 Replies
View Related
Nov 18, 2015
I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
View 2 Replies
View Related
Feb 19, 2008
Note the code below, running on the version noted.
I just found this today, figured I'd share. Not sure if it's a known bug or a "special" feature. The only difference between the two queries is the 3rd line, everything else is the same. Notice that the sort order changes, yet no errors or warnings are given. I assume that the table aliases are ignored for the order by, unless there are duplicate column names in the results.
I abstracted this from a bug I discovered in one of my apps today, where I have sales reps assigned to their clients with start and end dates. A while back, a developer asked me to format the dates without the time portion, and when I did so, I introduced the problem. I resolved it temporarily by ordering by convert(datetime, startdate) but I found it strange that the column alias match overrides the table alias attempted match in the order by. Another way to get around this would be to change the column aliases, then the sort order would be as desired, but I didn't want to have to change the app code for something so trivial.
My apologies if this is a duplicate.
@@version = Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
) as table_alias
order by table_alias.startdate
select convert(varchar(10), table_alias.startdate, 101) as startdate,
convert(varchar(10), table_alias.enddate, 101) as enddate,
table_alias.startdate, table_alias.enddate
from
(
select convert(datetime, dateadd(mm, -4, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -4, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -3, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -3, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -2, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -2, getdate())) as enddate
union
select convert(datetime, dateadd(mm, -1, getdate()-1)) as startdate, convert(datetime, dateadd(mm, -1, getdate())) as enddate
union
select convert(datetime, getdate()-1) as startdate, convert(datetime, getdate()) as enddate
) as table_alias
order by table_alias.startdate
View 4 Replies
View Related
Jan 7, 2007
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
Thank you, Mark
View 7 Replies
View Related
Sep 24, 2012
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
View 4 Replies
View Related
May 19, 2015
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
View 8 Replies
View Related