Group By ? 1

[jalbao]

In my db table i have a field (called dtAdded) that contains date/time values. An example of a value in the field dtAdded would be; 01/19/2001 12:35:58. Notice that it keeps trak of month/day/year hour:min:second. My question is, how can I use the SQL Group By to group records by the month/day/year using the dtAdded field. In other words, I can't run
SELECT * FROM TABLE GROUP BY dtAdded, because the GROUP BY will group only the dtAdded values that are the exact same down to the second. I want to group records that are restricted only by a specific day ie. month/day/year.

You may ask, "Why not get rid of the time in dtAdded?". Well, I want the end user to choose how he wants to group records together - by day, month, year, hour, minute or second.

 

[redlam]

you can group by convert(varchar,dtAdded,1) like this:
select <field_name>,
convert(varchar,dtAdded,1) as DateAdded
from <table_name>
group by
convert(varchar,dtAdded,1)

 

[redlam]

oops! you need to include the other field in the group by clause like this:
select <field_name>,
convert(varchar,dtAdded,1) as DateAdded
from <table_name>
group by
<field_name>, convert(varchar,dtAdded,1)

 

[jalbao]

thanks for the quick response redlam!

your solution works perfect

 

[jalbao]

redlam - how can i change the query so that i can Group By hours..

or maybe it would be easier if you can explain what convert(varchar,dtAdded,1) is actually doing. In other words, I don't understand why/how convert(varchar,dtAdded,1) extracts JUST the date..

 

[redlam]

the convert function converts the datetime value to a varchar data type. this function takes 3 parameters, data type, expression, and style -> convert(<data type>, <expression>, <style>). when style = 1, the result is formatted like mm/dd/yy, when style = 8, the result is formatted like hh:mm:ss BUT you may want to use the datepart function for grouping by hours. the datepart function takes 2 parameters - date part, and date expression -> datepart(<datepart>,<date>). if i understand correctly, you will want to group by the date and hour which would look like this:

select <field_name>,
convert(varchar,dtAdded,1) as DateAdded,
datepart(hh,dtAdded) as HourAdded
from <table_name>
group by
<field_name>,
convert(varchar,dtAdded,1),
datepart(hh,dtAdded)

 

[jalbao]

thanks a bunch redlam - excellent explanation

 

[redlam]

you're welcom albao ;-)

 

[jalbao]

it's me again redlam!

when i run the datepart() function everything works great unless i part by minutes... ie. datepart(mm,dtAdded) AS MinAdded. When I run this I will always get a value of '4' in my output. In fact, the output only gives one return even though I have aprox. 50 rows of data. I'm running the exact query that you suggested (without <field_name> of course). This problem only happens when I try to part by minutes...

do you have any ideas why this is happening?

 

[redlam]

i know, i know!
datepart(mm,dtAdded) is giving you the MONTH, not the minute.
this should give you the minute:
datepart(n,dtAdded) or datepart(mi,dtAdded)

here's a list of datepart abbrev from books on line:

Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
hour hh
minute mi, n
second ss, s
millisecond ms

 

[jalbao]

redlam you kick a** - thanks again