SQL server, Order by datetime value

Question

I am trying to sum the value by date, here is the query i currently have,

SELECT SUM(LineTotalValue) AS Value, DateTimeCreated FROM SOPOrderReturnLine AS SOPOrderReturnLine WHERE (AnalysisCode1 LIKE 'value%') GROUP BY DateTimeCreated ORDER BY DateTimeCreated desc

Here is the what what data looks like from the results of the query.

 Value DateTimeCreated 433.00 2015-01-26 15:36:28.723 135.00 2015-01-26 15:36:13.883 600.00 2015-01-26 15:28:14.957 0.00 2015-01-26 14:45:57.920 58.25 2015-01-26 14:45:21.080 39.08 2015-01-26 14:45:13.443 41.56 2015-01-26 14:45:07.010 99.80 2015-01-26 14:44:56.243 99.99 2015-01-26 14:44:48.590 75.00 2015-01-26 14:44:39.647

As you can see as the DateTimeCreated value has the times in it it wont count the value correctly what i want it to do is count based on the date, how to achieve this? changing the data or table design is not an option

Cast your datetime to a date and group by / order by that

Allan S. Hansen
– Allan S. Hansen

2015-01-27 09:48:54 +00:00
Commented Jan 27, 2015 at 9:48 — Allan S. Hansen
– Allan S. Hansen, Commented Jan 27, 2015 at 9:48

juergen d · Accepted Answer · 2015-01-27 09:49:13Z

SELECT SUM(LineTotalValue) AS Value, CONVERT(date, DateTimeCreated) FROM SOPOrderReturnLine AS SOPOrderReturnLine WHERE AnalysisCode1 LIKE 'value%' GROUP BY CONVERT(date, DateTimeCreated) ORDER BY CONVERT(date, DateTimeCreated) desc

mck · Accepted Answer · 2015-01-27 09:53:06Z

You can try this

SELECT SUM(LineTotalValue) AS Value, CAST(DateTimeCreated AS DATE) FROM SOPOrderReturnLine AS SOPOrderReturnLine WHERE AnalysisCode1 LIKE 'value%' GROUP BY CAST(DateTimeCreated AS DATE) ORDER BY CAST(DateTimeCreated AS DATE) desc

Fran · Accepted Answer · 2015-01-27 09:50:45Z

You can cast the values of DateTimeCreated to Date type, just modify the group by:

GROUP BY CAST(DateTimeCreated AS DATE)

KumarHarsh · Accepted Answer · 2015-01-27 11:14:59Z

this query will also utilise index if any,

SELECT SUM(LineTotalValue) AS Value, DateTimeCreated FROM SOPOrderReturnLine AS SOPOrderReturnLine WHERE (AnalysisCode1 LIKE 'value%') GROUP BY dateadd(d,0,datediff(day,0,DateTimeCreated)) ORDER BY DateTimeCreated desc

Collectives™ on Stack Overflow

SQL server, Order by datetime value

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related