Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Essential
Starting Member
2 Posts |
 Posted - 2009-06-23 : 00:42:51
|
| Hi all,I'm using sql server express 2005 and I'm struggling with grabbing a set of data grouped by date. I have a table, PaidExpense, with a "DatePaid" column. This column is the date and the time the expense was entered.I need to group the records by the datepaid column for each day. The problem is that it's also including the time entered in the group. Is there a way to ignore the time stamp and just group by the date?Here is what my sql query looks like:Select DatePaid, Sum(AmountPaid) As AmountPaid From PaidExpenses Where DatePaid > '3/12/2009 00:00:00' And DatePaid < '3/12/2009 23:59:59' Group By DatePaidAs you can see I'm specifying an exact date for testing and it's taking 16 records and grouping them down to 3, based on the 3 different time stamps on that date that the data was entered.At this point I don't think getting rid of the timestamp on the datepaid column is an option so I'm hoping that one of the experts here can give me a hand.Thanks! |
|
|
Mangal Pardeshi
Posting Yak Master
110 Posts |
Posted - 2009-06-23 : 01:03:39
|
| Try thisSelect DATEADD(DD,DATEDIFF(DD,0,DatePaid),0) AS Date, Sum(AmountPaid) As AmountPaid From PaidExpenses Where DatePaid > '3/12/2009 00:00:00' And DatePaid < '3/12/2009 23:59:59' Group By DATEADD(DD,DATEDIFF(DD,0,DatePaid),0)Basically just remove the time portion from datetime. You can also use a CONVERT funtion - CONVERT(VARCHAR(10),DatePaid,120)Mangal Pardeshihttp://mangalpardeshi.blogspot.com |
 |
|
|
nirene
Yak Posting Veteran
98 Posts |
Posted - 2009-06-23 : 01:07:03
|
| group by convert(varchar,datepaid,103) |
|
|
|
Essential
Starting Member
2 Posts |
Posted - 2009-06-23 : 01:13:31
|
| Wow thanks you guys for the very fast response.Mangal: I decided to use the 'convert' function because it was just easier for me to mentally digest. It seemed to make more sense. Here is what it looks like:SELECT CONVERT(varchar, DatePaid, 101) AS DatePaid, SUM(AmountPaid) As AmountPaidFROM PaidExpensesWhere DatePaid > '3/12/2009 00:00:00' And DatePaid < '3/12/2009 23:59:59'GROUP BY CONVERT(varchar, DatePaid, 101)Thank you so much to both of you for helping me with this! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-23 : 02:06:17
|
Keep datatypes as originally intended and handle formatting at the client.You should also use an open-ended datetime search criteria as displayed below.All these suggestion is only for making your query run faster!SELECT DATEADD(DAY, DATEDIFF(DAY, 0, DatePaid), 0) AS DatePaid, SUM(AmountPaid) As AmountPaidFROM PaidExpensesWhere DatePaid >= '20090312' And DatePaid < '20090313'GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, DatePaid), 0)
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-23 : 02:10:58
|
quote:
Originally posted by Essential
I decided to use the 'convert' function because it was just easier for me to mentally digest.
Show mw how you are going to sort the result, using CONVERT approach...
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
How to Group By 'Date' column with time?