.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

T-SQL 2005 datetime as Mmm-YYYY and proper Order By?

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
Hi I have datetime column in SQL 2005 table. I want to convert that date time into Mmm-YYYY format i.e. Jan-2010, May-2010, Dec-2010 and then proper ORDER BY in month ordering ( in my actual query I want data to be GROUP BY month & year) Table has OrderDate colmin with type datetime Query result output I am looking is : OrderDate       OrderDateMonthYYYY   ORDER BY too 2010-01-25     Jan-2010 2010-02-15     Feb-2010 2010-08-15     Aug-2010 Any T-SQL 2005 Pls…  

View Complete Post

More Related Resource Links

How Convert Datetime (YYYY/MM/dd HH:mm:ss) To yyyy-MM-dd



How do you write a date value to an XMLElement in .NET 3.5 XMLDocument object. 


 I am using the following:


DateTime orderDate = new DateTime();
orderDate = DateTime.Now; // 2010-08-24 16:45:46

string st = XmlConvert.ToString(orderDate, "yyyy-MM-dd");     // st="2010-08-24"
DateTime dt2 = XmlConvert.ToDateTime (st, "yyyy-MM-dd");   // dt2 = "2010-08-24 00:00:00"

I would like to remove the time section "00:00:00". I only need to write a date in the XML file with the following format :


Order date is defined like this in the xsd file : <xs:element name="OrderDate" type="xs:date"/>


Thanks for your support.

"Out-of-Range" Error When Passing DateTime (DD/MM/YYYY) into Stored Procedure

Hi everybody, I hope some of you will be able to help me regarding this datetime problem. The query in the stored procedure doesn't accept DD/MM/YYYY format, although all dates are being saved in the format of DD/MM/YYYY hh:mm:ss. Example of query that throws the error: @sDate = '31/8/2010' @eDate = '1/9/2010' SELECT * FROM tb_Schedule where scheduledate >= @sDate and scheduledate <= @eDate  Error:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value FYI, the SQL Server 2005 is defaulted to British English(2057)for all logins and the DB server itself and my server Regional and Language is set to English (United Kingdom). Any help will be appreciated. Thank you very much.  

Create a datetime object as dd/mm/yyyy

Hi i want to create a datetime object in the way dd/mm/yyyy but the defualt behaviour of datetime object is mm/dd/yyyy. I want this because my user enters date in dd/mm/yyyy format and i want to apply different additions and subtrations on this format. I know how to display dates in different formats. 

SSRS 2005 and Sort Order

I have a table and want to sort the 1st column which is called "WorkType" in a particular order. At present it looks like this.

Admin Tasks
Leave & Public Holiday

Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration



We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

our client Decided to stop migration, Please some help is there any other alternative.



slective order by in sql server 2005 query

except ascending or decending can we define our own choice of order by, like if i set order by hour it display 0(zero) first, but if time start form 1800 then how to display 18 first then 19 then 20 then so on to 5am can any body help in sql 2005 server

MS SQL 2005 - order by collate with subquery fails



This is a simplified version of a real query to show the problem. It can be executed on the sample Adventureworks database:


SELECT Person.Address.*,
 (SELECT TOP 1 [Name]
 FROM Person.StateProvince
 WHERE Person.StateProvince.StateProvinceId = Person.Address.StateProvinceId AND Person.StateProvince.TerritoryId IN (5, 6, 7)
 ORDER BY Person.StateProvince.TerritoryId) AS [Name]
FROM Person.Address

This works well, but my problem is that if I add collation to the order by field, I get a strange error message:

SELECT Person.Address.*,
 (SELECT TOP 1 [Name]
 FROM Person.StateProvince
 WHERE Person.StateProvince.StateProvinceId = Person.Address.StateProvinceId AND Person.StateProvince.TerritoryId IN (5, 6, 7)
 ORDER BY Person.StateProvince.TerritoryId) AS [Name]
FROM Person.Address


And the error is:


Msg 207, Level 16, State 1, Line 7
Invalid column name 'Name'.


The query with the collate clause works well if there's no subquery, but I need it to be like this (for a complicated reason, but please believe me :)).

Does anybody

dynamic order by deprecated in ms sql 2005?


Hi, I am pretty sure the code below compiled on ms sql 2000 and it doesn't compile on ms sql 2005.  I know the code below won't compile (create a proc if code below is in the proc) in ms sql 2005.   Is there any replacement short of exec (@sql1).  I hate execs.






CASE LEFT(@sort,1)



WHEN 'T' THEN f.oid

WHEN 'F' THEN fromoid

WHEN 'L' THEN c.acct

WHEN 'I' THEN e.item


CASE substring(@sort,1,1)

WHEN 'T' THEN f.oid

WHEN 'F' THEN fromoid

WHEN 'L' THEN c.acct

WHEN 'I' THEN e.item


CASE substring(@sort,2,1)

WHEN 'T' THEN f.oid


Proper order of a class members, generated via System.CodeDom


I use System.CodeDom to dynamically create a C++/CLI class and fill it with fields and methods. 

When I fill methods with statements, everything is fine. However, when I insert class fields, I run into a problem of order.


For example, the following lines

CodeTypeDelegate delegateDecl = new CodeTypeDelegate("delegate_name");

CodeMemberField delegateField = new CodeMemberField(new CodeTypeReference(delegateDecl.Name), "delegate_field");

codeType.Members.Add(delegateDecl);    /// the order of insertion - delegate declaration first !!!

codeType.Members.Add(delegateField);   /// the order of insertion - field with the delegate usage second!!!

generates the following code:

    public ref class classA


How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.


How to Encrypt and Decrypt a Password using SQLSERVER 2005?(Video)

Encypt and Decrypt a Password using SQLSERVER 2005(Video)

SQL Server 2005 Try and Catch Exception Handling

I'm pretty excited to see that there is some real error handling for T-SQL code in SQL Server 2005. It's pretty painful to have your wonderfully architected .NET solution tainted by less-than-VBScript error handling for stored procedures in the database. The big difference being the addition of TRY..CATCH blocks. Let's take a look:

Essential SQL Server Date, Time and DateTime Functions

The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

datetime server



I'd like to have the date and time from server.

DateTime.Now Is ok?

Must be something else?


sql server 2000 vs 2005


i would like to ask what the difference between sql server 2000 and 2005 



Is it possible with SQL sorted in descending order according to the word?
In Table I shrajeno Mastercard, Visa, Diners etc ...

All Visa are the first in the order, another Mastercard, etc. ...

Something does not work: ORDER BY PaymentType = 'Visa';

I would not want to work in one field PaymentId (111,222,33, etc. ..) and then use the ORDER BY.

Is this possible in such a way?

How to save Unicode in SQL Server 2005

I am using google transliteration to display the text in Hindi/Tamil

I tried to store the text appear as Hindi from textarea in an nvarchar
field of database (SQL Server 2005) , the text saved as "???????".

After retireving text from the same it display the text as "???????".

Can anyone help me how to store and retrieve the actual data from SQL
Server 2005?

I am using ASP.net,VB.Net and SQL Server 2005

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend