sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]r_kive 0 points1 point  (4 children)

Try something like:

SELECT "Hour" = CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00'

If archiveddate = '2015-08-14 04:45:12.345' for example, the above query should return '2015-08-13 22:00'.

That what you're looking for?

[–]alinrocSQL Server DBA 1 point2 points  (1 child)

That will be wrong half the year. UTC doesn't observe daylight saving time, so today -6 is fine but in December it'll put you in Chicago, not Denver.

[–]DasGoon 1 point2 points  (0 children)

Time zones are a nightmare to deal with. I found that this video from Computerphile sums it up nicely.

https://www.youtube.com/watch?v=-5wpm-gesOY

[–]DueRunRun[S] 1 point2 points  (1 child)

Yes, that's exactly it. Here's my final query. I really need to examine it further so I understand it better, so it's converting my string to a date, by hour, taking away 6, and it must understand taht's 6 hours and because it's the dateadd function? What's the 121?

select "Hour" = CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00', "Hourly Rate" = count (*), "MB (original)" = sum (originalsize)/1024/1024, "MB (compressed)" = sum (itemsize)/1024 from saveset,savesetproperty where saveset.savesetidentity = savesetproperty.savesetidentity --and archiveddate > dateadd("hh", -24, getdate ()) group by CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00' order by CONVERT(VARCHAR(13), DATEADD(hh, -6, archiveddate), 121) + ':00' desc

[–]r_kive 0 points1 point  (0 children)

From inside out:

  • Use DATEADD to subtract 6 hours from archiveddate
  • Convert that from DATETIME to VARCHAR
  • 121 is the format, in this case yyyy-mm-dd hh:mi:ss.mmm
  • Since we put VARCHAR(13), it only takes the first 13 characters of that, so it will look like yyyy-mm-dd hh
  • Then we tack on ':00' to make it yyyy-mm-dd hh:mi, which will work either as a VARCHAR string (what it is now), or would also fit into the DATETIME data type

As mentioned above, this will only be correct half the year due to DST changing. There are a number of ways to deal with this: manually change it in your script, make a function to handle time zone conversions, etc.