|
I want to create some volume metrics, and I need to produce a report
that shows how many rows were inserted by minute into a particular
table.
This is a candidate for a simple GROUP BY select, except that that the
INSERT_DT column in the table goes down to the second. I want to
GROUP BY at the minute level. I don't know an easy way to simply
truncate the seconds from the datetime. Extracting the time alone
won't work, because I want to compare minutes from different dates
(e.g. I am not interested in finding out if 12:47 of each day is the
highest volume minute, but rather that 23:14 of a particular day had
the highest number of inserts).
I did something ugly that works, but there has to be a better way.
Here's what I used:
cast(datename(year,INSERT_DT)+'-'+datename(month,INSERT_DT)
+'-'+datename(day,INSERT_DT)+' '+datename(hour,INSERT_DT)
+':'+datename(minute,INSERT_DT) as datetime)
It seems very strange to pull the components out of the original
datetime column, re-assemble them (sans minutes) with the stupid
dashes, spaces and colons into a string, and then re CAST them back
into a datetime.
What is the simpler way?
Thanks,
Bill | |
Share:
|
"bill" <bi**********@gmail.comwrote in message
news:f7**********************************@l42g2000 hsc.googlegroups.com...
>I want to create some volume metrics, and I need to produce a report
that shows how many rows were inserted by minute into a particular
table.
This is a candidate for a simple GROUP BY select, except that that the
INSERT_DT column in the table goes down to the second. I want to
GROUP BY at the minute level. I don't know an easy way to simply
truncate the seconds from the datetime. Extracting the time alone
won't work, because I want to compare minutes from different dates
(e.g. I am not interested in finding out if 12:47 of each day is the
highest volume minute, but rather that 23:14 of a particular day had
the highest number of inserts).
I did something ugly that works, but there has to be a better way.
Here's what I used:
cast(datename(year,INSERT_DT)+'-'+datename(month,INSERT_DT)
+'-'+datename(day,INSERT_DT)+' '+datename(hour,INSERT_DT)
+':'+datename(minute,INSERT_DT) as datetime)
It seems very strange to pull the components out of the original
datetime column, re-assemble them (sans minutes) with the stupid
dashes, spaces and colons into a string, and then re CAST them back
into a datetime.
What is the simpler way?
Thanks,
Bill
CAST(INSERT_DT AS SMALLDATETIME)
--
David Portas | | |
On Sun, 26 Oct 2008 12:27:08 -0700 (PDT), bill wrote:
(snip)
>It seems very strange to pull the components out of the original datetime column, re-assemble them (sans minutes) with the stupid dashes, spaces and colons into a string, and then re CAST them back into a datetime.
What is the simpler way?
Hi Bill,
In addition to the methods presented by David and Erland, here's one
more:
DATEADD(minute,
DATEDIFF(minute, '20080101', INSERT_DT),
'20080101');
This method can easlliy be adapted to strip off other parts of the date.
For instance, change "minute" to "hour" (twice) to strip off minutes and
get the last whole hour.
The principle used is to calculate the number of minutes that have
passed since some base date/time (in this case: midnight, Jan. 1st 2008)
and then add that number back to the same base date/time. You can use
any base date you like, just make sure to avoid overflows (for instance,
the number of seconds since 1900 is more than the maximum integer
stores).
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis | | |
Thanks everyone for the suggestions. It will save me time in the
future. Where can I find a comprehensive list of the Functions, their
syntax and arguments in SQL 2008? I tried googling "SQL Server
Function List" and the like without much luck. Couldn't find such a
list in BOL either. I'm sure it's there, and I am just using the
wrong search terms.
For clarity sake, I am looking for a something like this: http://www.psoug.org/reference/builtin_functions.html only for SQL.
These things are all over the web for Oracle, but must be indexed
under diferent search terms for SQL Server.
Thanks,
Bill | | |
In SQL Server 2008 Books Online (August 2008) there is a page titled
Functions (Transact-SQL). That page lists a variety of broad
categories of functions, each with a link to another page for the list
of functions in that category.
Roy Harvey
Beacon Falls, CT
On Wed, 29 Oct 2008 13:24:20 -0700 (PDT), bill
<bi**********@gmail.comwrote:
>Thanks everyone for the suggestions. It will save me time in the future. Where can I find a comprehensive list of the Functions, their syntax and arguments in SQL 2008? I tried googling "SQL Server Function List" and the like without much luck. Couldn't find such a list in BOL either. I'm sure it's there, and I am just using the wrong search terms.
For clarity sake, I am looking for a something like this: http://www.psoug.org/reference/builtin_functions.html only for SQL. These things are all over the web for Oracle, but must be indexed under diferent search terms for SQL Server.
Thanks,
Bill
| | |
Try:
Cast(INSERT_DT As Smalldatetime)
"bill" <bi**********@gmail.comwrote in message
news:f7**********************************@l42g2000 hsc.googlegroups.com...
>I want to create some volume metrics, and I need to produce a report
that shows how many rows were inserted by minute into a particular
table.
This is a candidate for a simple GROUP BY select, except that that the
INSERT_DT column in the table goes down to the second. I want to
GROUP BY at the minute level. I don't know an easy way to simply
truncate the seconds from the datetime. Extracting the time alone
won't work, because I want to compare minutes from different dates
(e.g. I am not interested in finding out if 12:47 of each day is the
highest volume minute, but rather that 23:14 of a particular day had
the highest number of inserts).
I did something ugly that works, but there has to be a better way.
Here's what I used:
cast(datename(year,INSERT_DT)+'-'+datename(month,INSERT_DT)
+'-'+datename(day,INSERT_DT)+' '+datename(hour,INSERT_DT)
+':'+datename(minute,INSERT_DT) as datetime)
It seems very strange to pull the components out of the original
datetime column, re-assemble them (sans minutes) with the stupid
dashes, spaces and colons into a string, and then re CAST them back
into a datetime.
What is the simpler way?
Thanks,
Bill
| | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
1 post
views
Thread by Sorisio, Chris |
last post: by
|
3 posts
views
Thread by bredal Jensen |
last post: by
|
5 posts
views
Thread by Salad |
last post: by
|
3 posts
views
Thread by nriesch |
last post: by
|
1 post
views
Thread by Prabhu |
last post: by
|
3 posts
views
Thread by Hrvoje Voda |
last post: by
|
3 posts
views
Thread by Andrew S. Giles |
last post: by
|
2 posts
views
Thread by Jon |
last post: by
|
7 posts
views
Thread by TheLostLeaf |
last post: by
| | | | | | | | | | |