Group by Date....

Question

Post reply

Group by Date....

b_boy

SSCertifiable

Points: 5145
More actions
September 19, 2008 at 4:42 am

#193040

Hello
I have been trying to group a query by date, but anytime i try the default database date appears, can anyone help?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

Check you data. Might be having default date.

Omit the same if you do not want that default date in where condition.

John H Marsh SSChampion Points: 11671 More actions · Answer 2

Hello,

Sorry, but I don’t really understand what your question is. Can you post your query?

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

b_boy SSCertifiable Points: 5145 More actions · Answer 3

Thanks all

I used the foloowing which worked for me:

SELECT SUBSTRING(CAST(OrderDate AS VARCHAR),1,11) AS OrderDate, .......

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

The proper way would be

group by dateadd(day,datediff(Day,0,date_col),0)

Madhivanan

Failing to plan is Planning to fail

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Madhivanan (9/19/2008)
The proper way would be
group by dateadd(day,datediff(Day,0,date_col),0)

For group by, it doesn't necessarily need to be cast back to a datetime:

datediff(Day,0,date_col)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

b_boy SSCertifiable Points: 5145 More actions · Answer 6

sorry guys but my own syntax worked perfect for me, tried yours but got incorrect results....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Not at all b_boy, it's a learning experience for us all. How about posting up some sample data and results? Everybody would appreciate this.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

b_boy (9/19/2008)
sorry guys but my own syntax worked perfect for me, tried yours but got incorrect results....

If you used a conversion to group by date, then you probably did it the slow way... the ones that use DATEADD and/or DATEDIFF will be faster.

Instead of us guessing, would you please post your complete query and maybe some test data so we can help better? Please see the link in my signature below for what we really need to help you on this and other problems. Thanks. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)