SQL Reporting - convert datetime to date only format

Your SQL Statement looked correct, can you post the error message your getting?

Actually, take out the GROUP BY statement and see if it works, don't GROUP BY the alias name that you have given the column..

SELECT     CONVERT(Varchar(10), DateOfHire, 101) AS DOHDateOnly, FirstName, LastName, State
FROM         EMPL
GROUP BY DateOfHire, FirstName, LastName, State

-Navicerts

Well, I found some sort of resolution but it isn't really adequate...

With

SELECT     CONVERT(varchar(10), DateOfHire, 101) AS 'MM/DD/YY', FirstName, LastName, State
FROM         EMPL

and then specifying GROUP BY MM_DD_YY

BUT THE PROBLEM NOW is that since it is converted to a string, the sorting order is different and not what I want.  I want to be able to print the records in sort order of ascending date, but of course, I am getting all the Januarys first, etc.  ARRRRRRRRRRRGGGGGGGGGGGGGGHHHHHH!!!

Any thoughts on how to get this to happen?  I am sure that I somehow need to break it into pieces to get to the year first, etc but not sure of the right approach.

Thanks for any additional help.

Try simply

SELECT     CONVERT(varchar(10), DateOfHire, 101) AS 'MM/DD/YY', FirstName, LastName, State
FROM         EMPL
ORDER BY DateOfHire

Navicerts,

This works great as a normal SQL query but not under SQL Reporting.  It still sorts in character rather than date order.  Do you know why it would have a different result under REporting Services???

THanks very much!
zoseri

Actually, Navicerts, that did sort of work.  THe only issue is that I wanted to be able to group them by date, and that is where it goes awry and uses character sort order.

As long as I am happy with having the dates simply listed, and not "grouped" then I can at least get the data in the right order.

However, my report would look a lot nicer if I were able to group by ascending date.  Otherwise, it's kind of a ...data grid.

If this is what we have to live with for now, so be it.  But if this tidbit of information jogs anything in your memory about how to get them sorted AND grouped, would be much appreciated.  

Thanks for all your help so far.  I'll wait a bit to see if you have any further wisdom to share, before awarding the points...

THanks again
zoseri

Hi Experts ,

I face the same problem but Im using Oracle as my database and this solution doesnt work for me as Oracle doesnt have the equivalent of this function.

Any ideas???

This thread if over 3 years old :)  I only noticed because it generated an email to me.  You may want to make a new question and see if anyone knows in the Oracle forum.  Maybe check this out too...  http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

Select DATENAME(month,Date) + '-' +
 DATENAME(day,Date) + '-' +
 DATENAME(Year, Date) AS [MyDate]
From TableX
...