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 |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2009-02-18 : 11:29:49
|
| I wanted to share a solution, and ask if there might be a better one...I wanted to truncate the time from a datetime field, so that I could order by just on the day of the datetime...with a secondary order by on the metricID field..this would show me all metrics enterd by day, ordered by metricid...my problem was the time in the timestamp would screw up the orderby....so I wanted to strip out the time...I used a cast/floor/cast combination...whatchathink?SELECT KeyID, CAST(FLOOR(CAST(ReportWeek AS float)) AS datetime) AS Reportweeks, MetricIDFROM ScoreCardMetricsORDER BY reportweeks DESC, MetricID |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2009-02-18 : 11:34:42
|
| select dateadd(day,datediff(day,0,getdate()),0) will strip out the timeJim |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-18 : 11:42:53
|
quote:
Originally posted by dlorenc
I wanted to share a solution, and ask if there might be a better one...I wanted to truncate the time from a datetime field, so that I could order by just on the day of the datetime...with a secondary order by on the metricID field..this would show me all metrics enterd by day, ordered by metricid...my problem was the time in the timestamp would screw up the orderby....so I wanted to strip out the time...I used a cast/floor/cast combination...whatchathink?SELECT KeyID, CAST(FLOOR(CAST(ReportWeek AS float)) AS datetime) AS Reportweeks, MetricIDFROM ScoreCardMetricsORDER BY ReportWeek DESC, MetricID
modify like above and see |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-02-18 : 12:19:40
|
| Jim, your solution worked also...is there any advantage in optimization to your solution to the other?..SELECT KeyID, dateadd(day,datediff(day,0,reportweek),0) AS datetime, MetricIDFROM ScoreCardMetricsORDER BY datetime DESC, MetricIDvisakh, no...I cannot just order by reportweek, as that field is the one that contains the time in the timestamp...so with the time included, it cannot be sorted by 'day'...KeyID________reportweek_____________datetime____________MetricID148____2009-02-14 08:32:23.977__2009-02-14 00:00:00.000____17144____2009-02-14 08:32:21.803__2009-02-14 00:00:00.000____40151____2009-02-14 08:32:25.773__2009-02-14 00:00:00.000____41you can see in the above data..reportweek (because of the time) is useless to use in an order by...as each record is unique (it would be very rare to be able to store a record at exactly the same timestamp.)...so to order by day, I really need jim's solution to strip out the time (see the datetime field)...this then would allow me to order the metricid by day/metricid...to see that I only have one metric submitted per day.... |
|
|
|
|
|
|
|
|
Datetime, truncate time so that date be order by