Remove Seconds from Datetime: How To?

bill

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

Oct 26 '08 #1

David Portas

"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

Oct 26 '08 #2

Erland Sommarskog

David Portas (RE****************************@acm.org) writes:

CAST(INSERT_DT AS SMALLDATETIME)

Or CONVERT(char(16), INSERT_DT, 126)

These two are not equivalent. David's solution will round, mine will
truncate:

declare @d datetime
select @d = '20081212 23:00:45'
select convert(char(16), @d, 121)
select convert(smalldatetime, @d)
If you want the data to be surrected as a datetime value, change 121
to 126 in my solution.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Oct 26 '08 #3

Hugo Kornelis

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

Oct 27 '08 #4

bill

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

Oct 29 '08 #5

Roy Harvey (SQL Server MVP)

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

Oct 30 '08 #6

John Smith

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

Nov 2 '08 #7