Home » Asp.net

need of Ascending order based on the Date and month.

Hi friends,

query.Append("SELECT Name, Sex, Marital_Status, Dob, Anniversary, Mobile, Email FROM Client WHERE ");

            if (startDate > new DateTime() && endDate > new DateTime())
                query.Append("(DATEADD(YEAR,DATEDIFF(YEAR, DOB,'" + startDate + "'),DOB) BETWEEN '" + startDate + "'AND'" + endDate + "' OR DATEADD(YEAR,DATEDIFF(YEAR, ANNIVERSARY,'" + startDate + "'),ANNIVERSARY) BETWEEN '" + startDate + "'AND'" + endDate + "') AND ");
           if (!string.IsNullOrEmpty(maritalStatus) && !string.Equals("All", maritalStatus))
                query.Append(" Marital_Status = '" + maritalStatus + "'");

            string resultQuery = query.ToString();
            if (resultQuery.Trim().EndsWith("AND", StringComparison.OrdinalIgnoreCase))
                int lastIndex = resultQuery.LastIndexOf("AND");
                resultQuery = resultQuery.Remove(lastIndex, 3);

            if (resultQuery.Trim().EndsWith("WHERE"))
                int lastIndex = resultQuery.LastIndexOf("WHERE");
                resultQuery = resultQuery.Remove(lastIndex, 5);

            return resultQuery.Trim();        

I have used above code, This code working properly, but It  shows randomly,  
I need, 
The Data should have to display in ascending order based on the Date and month. 


2 Answers Found


Answer 2

 In the end,before return
append order  by clause.

Else explain with sample data that your query  return and you want sort on which colmn.



I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known.

We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date.

I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something.

For sorting, when records have same Year, I would have another column to do relative sorting...

So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any.

Thanks in advance.



Using the getdate() function, how do I select the last WHOLE 3 months,

So for example, for todays date (29/09/2010)

I want to return all records with the date between 01/06/2010 and 31/08/2010

Is this possible?


Hello, i am curious and also would like to know how to duplicate this user interface feature in Windows 7. I am aware that if you attempt to sort a column in Windows XP, there is no up/down arrow in the column.

Please have a look at http://img692.imageshack.us/img692/2082/listviewdescorder.png

It shows part of the task manager process list, and image name column is sorted in descending order.

What i am trying to find out is how is the arrow pointing downwards (representing descending) is created.

Am i able to reproduce this "arrow pointing downwards" in VS 2005?

Looking forward to the feedback!

When I add a new index I can also specify the "sort order" as ascending or descending. What does it mean? does it affect performance? does it mean that the binary search tree will be set in a certain way?

I am getting this error when I am trying to plot some dates.

I have 3 series of dates coming from the same dataset.

It is my belief that this is because some of the dates at the end of the list are blank. Is there a way to make the chart ignore points that have no dates, while still plotting the dates in the other series?

I know I can switch the Chart to 2D and it will plot them but I would get points that I don't want anyways because the dates are not valid.


Thanks for the help.


Hi Guys,

I have the following parameter

Dim sDate As String = "201003"

And i want to get the first date and last date of the month and convert it into integer value.

For Example:

Given Parameter: 201003

First Results




Second Results







Can someone please help me get around this query, I need to convert a date column in "Views" by returning the last date of the month (any month).

i.e. If the date shows 03-Mar-2010, I need to convert it to the last month which is 31-Mar-2010

Please assist :-(


Hi ...

I have two table Order and Order Detail.

 What i need is i need to  bind last 10 Order in Gridview/details view with paging, and the same order detail also fetch from database and shown in another gridview.. if user click any oderNo in first Gridview/DetailView then i need to show Respective order detail in anther gridview..

without postback coz we already fetch 10 order with orderdetails from the database..

and if user click next 10 orderNo using paging then again we need to load 10 orderNo with respective orderdeatils from database like this going on....

as well as let me know how to take print of particular oder and order detail....?


I have a pretty simple analytic chart on my sharepoint site. The columns are The name of the location, and the month, and then all of the data is in the rows.


so basically it looks like this

                  Place 1                                  Place 2

          jan feb mar april may june             jan feb mar april may june





You get the point. The problem is, I want to have two filters; one that filters by the locations, and one that filters by the months. When I created the locations filter and connected it to the grid, it made all of the months go in alphabetical order instead of the correct order, like so:


              place 1

         aug dec jul nov oct



Obviously, this is not how a calendar is supposed to look. I already changed the properties in my sql cube to make the months go in the correct order in the unfiltered chart. They are ordered by the key, which is month, instead of monthname. What can I do?

Also, I would like my second filter to filter by months as a multiple select tree. When I create that, it also orders the months alphabetically instead of correctly. How can I change that?

When below SQL is used to print the MONTH Names in SQL SERVER 2000, order by Month name ordering in Alphabetical order, which is incorrect.

Let me know any work around to get this order by Montly order and then Year.

SELECTCASEWHENMONTH(a.registrationdate) = 1 THEN'Jan'WHENMONTH(a.registrationdate) = 2 THEN'Feb'WHENMONTH(a.registrationdate) = 3 THEN'Mar'WHENMONTH(a.registrationdate) = 4 THEN'Apr'WHENMONTH(a.registrationdate) = 5 THEN'May'WHENMONTH(a.registrationdate) = 6 THEN'Jun'WHENMONTH(a.registrationdate) = 7 THEN'Jul'WHENMONTH(a.registrationdate) = 8 THEN'Aug'WHENMONTH(a.registrationdate) = 9 THEN'Sep'WHENMONTH(a.registrationdate) = 10 THEN'Oct'WHENMONTH(a.registrationdate) = 11 THEN'Nov'WHENMONTH(a.registrationdate) = 12 THEN'Dec'ENDAS monthname, 
        YEAR(a.registrationdate) ASYEAR, 



when a phone call activity is created from the lead entity

Workflow as to check the due date attribute for the phone cal activity (which is created from the lead entity) if the due date is less than 1 day from the current date

its as to create a task for a phone cal activity owner

How can i achieve this task

Reply as soon as possible





I need to update all the rows in a table with a date that increments by two months each time.

I.e.  Row1 should have a date of 1/3/2010,  Row2 = 1/5/2010, Row3 1/7/2010 

The tricky bit is that the user needs to be able to enter the row number, so that it can start on any row and the start date.

e.g. Input 7 and 1/9/2010 and the query update all rows from 7 onwards with date 1/9/2010 incrementing each time, so row8 is 1/11/2010 etc.

I know how to add months to a date, but I can't work out how to make the query work on the previous record.


Please help!!!


We have a month-end report that is run by each of our offices, the regional offices, the divisional offices, and at the global headquarters.  This report asks for a date range and returns the results based the actualclosedate of opportunity records.  Now a number of the opportunity records are closed on the last day of the calendar month and the issue that we are running into is that the last day of the month at some our locations is the first day of the next month for the some of the offices higher up in the office's reporting structure.  Thus, when a side-by-side comparison is done of the reports there is a variance in what is reported.  For example Office A is in CST time zone and it reports to Office B in Tokyo Time time zone.  If Office A closes records on 31 October they will show on the report run for 01 October – 31 October when the report is run in Office A; however, when the report is run in Office B some of the records will not be included as some of the records may reflect the actual close date as being 01 November.  This depends on the time of day the records are closed on 31 October.  This results in questions about which version of the report is accurate though both reports are accurate when you account for how time works.  Unfortunately, this is not the result that is desired by the business.

I had considered using the UTC time but this does not always reflect the intent of the user closing the opportunity.  For example, if a user in Office A closes an opportunity after 18:00:00 or 19:00:00, with day lights saving time, the UTC will reflect as being on the next day.  Though using UTC time would address the issue of records being included or not included when queried by date range, since it would standardized the date used, it introduces a new issue of not reflecting the intent of the user closing the opportunity. 

Ideally, I would evaluate the actualclosedateutc field by joining the UserSettings table on the modifiedby field and then using fn_UTCToTzSpecificLocalTime to get the date the user closing the record actually provided.  Unfortunately this will not work as workflows change the modifiedby field after the opportunity is closed.  Thus, the modified field does not always reflect the user whom closed the record.

Hopefully, someone on this forum will see something that I am missing and can provide a solution to address this issue.


I have this data that has a CaseDate column that gate populated every day BY DATE, my question is how can pull and filter data by day/month/year 30 days of a particuler month in SSIS to get porocessed each month. 





' TO 31-MAY-2010]





TO 31-JUNE-2010]





TO 31-JULY-2010]


hi gurus, how to calcuate months difference between two dates? pls let me know rgds

How do i set dates for default of pram value in the strored proc...


i'e i want the last month 6/2010  - for end date


then the first month of the year in 2010


I am running a query that displays Specimen Type Name , Count of Each Specimen Type Name and Specimen Received Date to generate my SSRS report.

The report displays a column specimenTypeName, and several columns for Specimen Recieved Date. For each SpecimenTypeName it displays the count of SpecimenTypesName recieved on that date. I want to modify the report layout (Matrix) so that the Specimen Recieved Date column is grouped by year and month with a little plus sign, so that it first displays the total number of that SpecimenTypeName recieved in that year, On clicking the plus sign it should display counts for each month in that year and on clicking the plus for a month it should display count for that month in that year.

Can I do this without modifying the query, at the report level?

Please help me to accomplish this. I checked the tutorial in the help section but did not find any help there.




query running:

SELECT APVX.DOS.SpecimenType.SpecimenTypeName      AS  SpecimenTypeName, count(APVX.DOS.SpecimenType.SpecimenTypeName) as count, APVX.ORDERS.Specimen.ReceivedDate
FROM         APVX.ORDERS.ComponentOrder INNER JOIN
                      APVX.ORDERS.ApCase INNER JOIN
                      APVX.ORDERS.TestOrder ON APVX.ORDERS.ApCase.CaseID = APVX.ORDERS.TestOrder.CaseID INNER JOIN
                      APVX.ORDERS.TestOrderSpecimen ON APVX.ORDERS.TestOrder.TestOrderID = APVX.ORDERS.TestOrderSpecimen.TestOrderID INNER JOIN
                      APVX.ORDERS.Specimen ON APVX.ORDERS.TestOrderSpecimen.SpecimenID = APVX.ORDERS.Specimen.SpecimenID ON
                      APVX.ORDERS.ComponentOrder.TestOrderID = APVX.ORDERS.TestOrder.TestOrderID INNER JOIN
                      APVX.DOS.SpecimenType ON APVX.ORDERS.Specimen.SpecimenTypeID = APVX.DOS.SpecimenType.SpecimenTypeID
WHERE APVX.ORDERS.ComponentOrder.ComponentID = 100129
group by APVX.DOS.SpecimenType.SpecimenTypeName, APVX.ORDERS.Specimen.ReceivedDate

hi ,

i have created the calendar list.I have added some birthdays in that calendar.Now i want to display the person name with greeting in that month before oneday.


I have a SQL view that use todays date and queries 12 months of data.  View works fine but I want to convert the format to a number that represents the latest month in this format "01", "02".  For example if I run the query in July 2010 then June 2010 is returned as "201006".  I need it formated to "01" which represents one month in the past.  "201001" would be "06" which is 6 months ago.  Any ideas?  Below is my view code.

SELECT     dbo.oehdrhst_sql.cus_no AS Customer, SUM(dbo.oehdrhst_sql.tot_sls_amt) AS Sold, SUBSTRING(CAST(dbo.oehdrhst_sql.inv_dt AS varchar(8)), 1, 6)
                      AS YYYYMM, COUNT(dbo.oehdrhst_sql.ord_no) AS Orders, dbo.ARCUSFIL_SQL.slspsn_no AS Salesman, dbo.ARSLMFIL_SQL.email_addr,
                      dbo.ARCUSFIL_SQL.cus_type_cd, (dbo.oehdrhst_sql.tot_sls_amt - dbo.oehdrhst_sql.tot_cost) / NULLIF (dbo.oehdrhst_sql.tot_sls_amt, 0) AS GP_prc,
                      dbo.oehdrhst_sql.tot_sls_amt - dbo.oehdrhst_sql.tot_cost AS GP_amt
FROM         dbo.oehdrhst_sql INNER JOIN
                      dbo.ARCUSFIL_SQL ON dbo.oehdrhst_sql.cus_no = dbo.ARCUSFIL_SQL.cus_no INNER JOIN
                      dbo.ARSLMFIL_SQL ON dbo.ARCUSFIL_SQL.slspsn_no = dbo.ARSLMFIL_SQL.slspsn_no
WHERE     (dbo.oehdrhst_sql.inv_dt BETWEEN CAST(SUBSTRING(CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 112), 1, 4)
                      - 1 AS char(4)) + CAST(SUBSTRING(CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1), GETDATE()), 112), 5, 4) AS char(4)) AND
                      CONVERT(varchar(25), DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)), 112)) AND (NOT (dbo.ARCUSFIL_SQL.slspsn_no IN ('S01', 'S02',
                      'S03', 'S04', 'S05', 'S06', 'S07', 'S08', 'S09', 'SN1')))
GROUP BY dbo.oehdrhst_sql.cus_no, SUBSTRING(CAST(dbo.oehdrhst_sql.inv_dt AS varchar(8)), 1, 6), dbo.ARCUSFIL_SQL.slspsn_no,
                      dbo.ARSLMFIL_SQL.email_addr, dbo.ARCUSFIL_SQL.cus_type_cd, dbo.oehdrhst_sql.tot_sls_amt, dbo.oehdrhst_sql.tot_cost


I need to produce stats from July to July inclusive, is there a simple way of producing with the sample code i have below?

                                (Month(SalesOrders.TransactionDate) = Month(DateAdd(Month,-11,GetDate()))) THEN SalesOrders.Quantity
                                ELSE 0 END) AS 'TotalMonth1'
                                (Month(SalesOrders.TransactionDate) = Year(DateAdd(Year,-1,GetDate()))) THEN SalesOrders.Quantity
                                ELSE 0 END) AS 'TotalMonth0'


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure