October 19, 2009 at 5:15 pm
Hey professionals,
I have a Date column "dateadded" that stores date down to the minute. so it stores a date like say 2009-10-19 15:58:00. I want to select some columns and group by only want year, month and day. I do not want the time. How do I use the datepart function?
For example if I only wanted to group by year, I would do this
select dateadded, max(column1) from Mytable
group by datepart(y, dateadded)
How do I tweak the code to group by year, month and day without the minutes and hours
Thanks
October 19, 2009 at 8:10 pm
The most efficient in terms of CPU cycles is probably the following:
dateadd(day,datediff(day, 0,getdate()),0)
This strips hours, minutes, seconds and milliseconds from the date. Just replace GETDATE() with the name of your date column.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 19, 2009 at 8:23 pm
Bob,
I really wish you'd quit beating me to answers tonight!:-D
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2009 at 9:05 pm
Even a blind squirrel finds some nuts, Jack. 😀
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 19, 2009 at 9:08 pm
Bob Hovious 24601 (10/19/2009)
Even a blind squirrel finds some nuts, Jack. 😀
Hmmm, I could take that a couple of different ways.:-P
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 19, 2009 at 9:35 pm
You've been watching Johnny Depp in "Charlie and the Chocolate Factory" haven't you? 😛
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 20, 2009 at 12:25 pm
Thanks for the help guys!
October 21, 2009 at 12:16 pm
Hey guys, i noticed that the query gave me some 1905 dates?? Is there a reason why?
October 21, 2009 at 4:13 pm
Not to sound sarcastic, but they show because there are 1905 dates in the table. They are probably typos, but you'll have to determine that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2009 at 10:29 am
i :-D, I also thought for a minute that there were 1905 records on the table but that was not the case. There are 3 tables that I am putting all the records into 1. The good news is that the code was not responsible for the 1905 records. So my apologies for that.
However something really strange is happening. Each record on the 3 tables earliest date is 2003. When i join them all together into 1 table, then I see 1905 records from no where. Very strange. Has this ever happened to anybody?
October 22, 2009 at 10:44 am
Could we see some code and sample data?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 23, 2009 at 5:28 pm
False alarm never mind. Sorry about that and thanks for all the help guys !!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply