After searching and reading some quite amusing posts 9 years old, it
seems that 9 years later SQL Server still doesn't have any TIME
specific formatting options.

I need to compare datetime data down to the minute accuracy (but no
more).

The solution seems to be via a variety of unpretty string
concatenations of datetime data, and to get the time part with leading
zeroes it seems one must include a concatenation of (in the simplest
form I can find):

SELECT CONVERT(char(5),@TEST,108) as TimeWithLeadingZeroes

ie.

DECLARE @TEST DATETIME
SET @TEST = '20080101 09:00:05'

SELECT CONVERT(varchar(12), @TEST, 112) + ' ' + CONVERT(char(5),@TEST,
108) as DateTimeWithLeadingZeroesInTimeHooray

---------
09:00


Is that really the simplest way though?

Cheers
Matt

Sorry that select returns:
20080101 09:00
actually (obviously).

On Jun 12, 1:46pm, Matty J <mattyjen...@gmail.com> wrote:
> After searching and reading some quite amusing posts 9 years old, it
> seems that 9 years later SQL Server still doesn't have any TIME
> specific formatting options.
>
> I need to compare datetime data down to the minute accuracy (but no
> more).
>
> The solution seems to be via a variety of unpretty string
> concatenations of datetime data, and to get the time part with leading
> zeroes it seems one must include a concatenation of (in the simplest
> form I can find):
>
> SELECT CONVERT(char(5),@TEST,108) as TimeWithLeadingZeroes
>
> ie.
>
> DECLARE @TEST DATETIME
> SET @TEST = '20080101 09:00:05'
>
> SELECT CONVERT(varchar(12), @TEST, 112) + ' ' + CONVERT(char(5),@TEST,
> 108) as DateTimeWithLeadingZeroesInTimeHooray
>
> ---------
> 09:00
>
> Is that really the simplest way though?
>
> Cheers
> Matt

Can you clarify "compare"? Given two datetime values, what exactly do
you need to do with them?

Matty,

Assuming you're comparing two date values for accuracy to the minute,
just do a DATEDIFF using Minute as the first parameter between the two
date/times...

SELECT DATEDIFF(minute, '6/12/2008 13:00:00', CURRENT_TIMESTAMP)

I should clarify further...

SELECT DATEDIFF(minute, '6/12/2008 13:00:00', CURRENT_TIMESTAMP)

If the times are the same (at the minute level) the select will return
0.

SELECT DATEDIFF(minute, '6/12/2008 13:01:00', '6/12/2008 13:01:59')
-- will equal 0 (59 seconds later)
SELECT DATEDIFF(minute, '6/12/2008 13:01:59', '6/12/2008 13:02:00')
-- will equal 1 (1 second later)

Hi there all, thanks for the replies.

In it's simplest form, if you could answer how to output, in SQL,
09:00
from a datetime column when the value is 9AM in the morning, that
would answer my question.

I'm worried to say more as it might become a red herring, however:
For my DateTimeStamp field, I want to GROUP the rows BY unique minute
and get a count of records in each minute.

(I then need to do a LEFT OUTER JOIN on the table to itself on this
'unique minute' value, but that is possibly beside the point).

What would be cool is a function something like:
SELECT FORMATDATE(MyDateColumn, 'yyyy-dd-mm:hh:mimi')
so we have complete control over the formatting by specifying a string
of exactly how we want the date formatted, and/or alternatively, in
addition to YEAR, MONTH, DAY functions, also HOUR, MINUTE, SECOND,
MILLISECOND

Thanks for any further .
Matt

> In it's simplest form, if you could answer how to output, in SQL,
> 09:00
> from a datetime column when the value is 9AM in the morning, that
> would answer my question.

DECLARE @d DATETIME;
SET @d = '9 AM';
SELECT @d, CONVERT(CHAR(5), @d, 108);

> For my DateTimeStamp field, I want to GROUP the rows BY unique minute
> and get a count of records in each minute.

Better to group by the minute using DATEDIFF then converting everything to
char. And not sure why the leading 0 is important in this case anyway.

> (I then need to do a LEFT OUTER JOIN on the table to itself on this
> 'unique minute' value, but that is possibly beside the point).

You should have a numbers and/or a calendar table. I'd point you to
articles on aspfaq.com about it, but apparently the site is being
blocked/warned by google and/or FireFox as it fell victim to the SQL
injection attack a few weeks ago (though it has since been cleaned up, as
far as I know).

Anyway you could try something like this to see a different approach than
the one you are attempting:





USE tempdb;
GO

CREATE TABLE dbo.Numbers
(
Number INT IDENTITY(0,1)
PRIMARY KEY CLUSTERED
);
GO

SET NOCOUNT ON;
GO

-- create a row_number for each minute
-- there are more efficient ways to do this
-- but for 1440 rows it should make no diff.
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 1439
BEGIN
INSERT dbo.Numbers DEFAULT VALUES;
END
GO

-- here is your "real" table
-- with a few rows of sample data
CREATE TABLE dbo.MattyJ
(
DateTimeStamp DATETIME,
[Description] NVARCHAR(32)
-- other columns
);
GO

INSERT dbo.MattyJ SELECT '20080601 00:00:25', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:00:26', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:00:27', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:00:28', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:00:29', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:01:25', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:02:25', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:03:00', 'bar';
INSERT dbo.MattyJ SELECT '20080601 00:03:06', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:03:55', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:04:25', 'foo';
INSERT dbo.MattyJ SELECT '20080601 00:04:29', 'foo';
GO

DECLARE @BaseDate SMALLDATETIME;
SET @BaseDate = '20080601';

SELECT
d = DATEADD(MINUTE, n.Number, @BaseDate),
c = COUNT(m.DateTimeStamp)
FROM
dbo.Numbers n
LEFT OUTER JOIN
dbo.MattyJ m
ON
m.DateTimeStamp >= DATEADD(MINUTE, n.Number, @BaseDate)
AND m.DateTimeStamp < DATEADD(MINUTE, n.Number+1, @BaseDate)
GROUP BY
DATEADD(MINUTE, n.Number, @BaseDate)
ORDER BY
d;

-- if you have SQL Server 2005, you can do this, which is
-- much easier to read and doesn't require multiple DATEADD
-- calculations:

WITH times(s, e) AS
(
SELECT
DATEADD(MINUTE, n.Number, @BaseDate),
DATEADD(MINUTE, n.Number+1, @BaseDate)
FROM
dbo.Numbers n
)
SELECT
d = times.s,
c = COUNT(m.DateTimeStamp)
FROM
times
LEFT OUTER JOIN
dbo.MattyJ m
ON
m.DateTimeStamp >= times.s
AND m.DateTimeStamp < times.e
GROUP BY
times.s
ORDER BY
times.s;
GO

DROP TABLE dbo.Numbers, dbo.MattyJ;

Crikey mate - talk about comprehensive - thanks a lot!

I've already learnt several things from your one post.

Good point about 'why is the leading zero important?' - I was going to
use the string version in my results to the application client,
however if I send the date to the client, 'they' can format it however
they want for ultimate output. 9AM was coming out as 9:0 which is not
very presentable.

I've got a calendar table, not a numbers one just yet.

As this is a lot of 'new' to take in (or maybe my brain is just being
lazy as it's Friday), I'm wondering what to do using your code but
when I want to match all records in the 'MattyJ' table in your example
and when I don't have a particular BaseDate? I guess the query's
performance decreases the 'longer ago' the BaseDate that I use, eg if
I need to use a fixed date of a date that is before the data in the
table starts, my numbers table needs to be larger and larger as time
goes on (in fact how big should my numbers table be).

I guess I could dynamically get a BaseDate by using a 'flag' column
and doing a MIN on the datetimestamp after grouping it on the same and
doing a WHERE for when the flag is not set...

Thanks again anyway.
Cheers
Matt

> lazy as it's Friday), I'm wondering what to do using your code but
> when I want to match all records in the 'MattyJ' table in your example
> and when I don't have a particular BaseDate? I guess the query's
> performance decreases the 'longer ago' the BaseDate that I use, eg if
> I need to use a fixed date of a date that is before the data in the
> table starts, my numbers table needs to be larger and larger as time
> goes on (in fact how big should my numbers table be).

Except for very straightforward cases, narrative usually doesn't very
much when trying to describe a desired result... I'm not quite sure what
you're after. Do you want one row for all data that occurs between 9:00 AM
and 9:00:59.997 AM, regradless of the date? So, if I had the following
sample data:

2008-06-01 09:00:32.453
2008-06-07 09:00:21.421
2008-06-09 09:00:35.997
2008-06-09 09:01:00.342

You'd want the following result?

09:00 3
09:01 1

Hi there Aaron, and thanks for your reply.

Essentially I need to highlight to the user any rows in the database
where there is more than one per minute per WHERE clause (including a
GROUP BY 'IP_Address'). An administrator will review the highlighted
rows via a web admin page, potentially record some details about these
suspect highlighted rows, and then do an action to the effect of 'Mark
these rows as reviewed' (which will change a boolean column's value
for those rows from 0 to 1. Note the WHERE clause of the SELECT
statement that highlights the rows with more than one per minute will
exclude any rows where the value = 1).

So if there was the sample data:

DATE IP_Address
2008-06-01 09:00:32.453 192.168.0.1
2008-06-07 09:00:21.421 192.168.0.1
2008-06-09 09:00:35.997 192.168.0.1
2008-06-09 09:01:00.342 192.168.0.1
2008-06-09 09:01:32.453 192.168.0.1
2008-06-09 09:01:44.421 192.168.0.1
2008-06-09 09:10:35.997 192.168.0.1
2008-06-09 09:10:55.342 192.168.0.1

Then the result would be
COUNT DATE IP_Address
3 2008-06-09 09:01 192.168.0.1
2 2008-06-09 09:10 192.168.0.1


Essentially, (note I'm using SQL2005) what you gave in your previous
example is what I want except preferably specifiying only a count
greater than 1 (per minute per IP_Address) rather than a "Base Date"
to start the query from (as I don't want to Admin person to ever not
see any rows where the count is greater than 1).

Any further would be greatly appreciated.
Cheers
Matt

And what if your sample data is:

DATE IP_Address
2008-06-07 09:01:32.453 192.168.0.1
2008-06-07 09:01:21.421 192.168.0.1
2008-06-09 09:01:00.342 192.168.0.1
2008-06-09 09:01:32.453 192.168.0.1
2008-06-09 09:01:44.421 192.168.0.1
2008-06-09 09:10:35.997 192.168.0.1
2008-06-09 09:10:55.342 192.168.0.1

You only treated 2008-06-09 in your sample result. I am still not clear if
you want "all rows that occurred in the same minute on different days" or
"all rows that occurred in the same minute in any single day."

Would the result from above ideally be:

09:01 192.168.0.1 5
09:10 192.168.0.1 2

Or would it be:

2008-06-07 09:01 192.168.0.1 2
2008-06-09 09:01 192.168.0.1 3
2008-06-09 09:10 192.168.0.1 2

?

Betting on the latter, and including cases where rows should show up but NOT
highlighted in the case that they are not "duplicates" in this sense *or*
they have already been marked as checked:


USE tempdb;
GO

CREATE TABLE dbo.Logs
(
dt DATETIME,
ip VARCHAR(15),
CheckedFlag BIT NOT NULL DEFAULT (0)
);
GO

SET NOCOUNT ON;
GO

INSERT dbo.Logs(dt, ip)
SELECT '2008-06-07 09:00:32.453','192.168.0.1'
UNION ALL SELECT '2008-06-07 09:01:32.453','192.168.0.1'
UNION ALL SELECT '2008-06-07 09:01:21.421','192.168.0.1'
UNION ALL SELECT '2008-06-09 09:01:00.342','192.168.0.1'
UNION ALL SELECT '2008-06-09 09:01:32.453','192.168.0.1'
UNION ALL SELECT '2008-06-09 09:01:44.421','192.168.0.1'
UNION ALL SELECT '2008-06-09 09:10:35.997','192.168.0.1'
UNION ALL SELECT '2008-06-09 09:10:55.342','192.168.0.1'
UNION ALL SELECT '2008-06-09 09:11:24', '192.168.0.1'
UNION ALL SELECT '2008-06-09 09:11:24', '192.168.0.2';
GO

INSERT dbo.Logs(dt, ip, CheckedFlag)
SELECT '2008-06-09 09:11:24', '192.168.0.5', 1
UNION ALL SELECT '2008-06-09 09:11:25', '192.168.0.5', 1;
GO

;WITH cooked_data
(
dt,
mn,
df,
ip,
CheckedFlag
)
AS
(
SELECT
dt,
DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)),
DATEDIFF(MINUTE, DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)), dt),
ip,
CheckedFlag
FROM
dbo.Logs
-- I hope you have a where clause here
-- and aren't scanning entire table
-- every time you run this report!
)
SELECT
[minute],
ip,
[RowCount],
HighlightThisRow = CASE
WHEN CheckedFlag = 0 AND c > 1 THEN 1
ELSE 0
END
FROM
(
SELECT
[minute] = DATEADD(MINUTE, df, mn),
ip,
CheckedFlag,
[RowCount] = COUNT(*)
FROM
cte
GROUP BY
DATEADD(MINUTE, df, mn),
ip,
CheckedFlag
) AS s
ORDER BY
[minute];
GO
DROP TABLE dbo.Logs;
GO






On 6/18/08 8:37 PM, in article
23c01006-c1be-43c0-8a44-b4ab7e645365...oglegroups.com, "Matty J"
<mattyjensen@gmail.com> wrote:

> Hi there Aaron, and thanks for your reply.
>
> Essentially I need to highlight to the user any rows in the database
> where there is more than one per minute per WHERE clause (including a
> GROUP BY 'IP_Address'). An administrator will review the highlighted
> rows via a web admin page, potentially record some details about these
> suspect highlighted rows, and then do an action to the effect of 'Mark
> these rows as reviewed' (which will change a boolean column's value
> for those rows from 0 to 1. Note the WHERE clause of the SELECT
> statement that highlights the rows with more than one per minute will
> exclude any rows where the value = 1).
>
> So if there was the sample data:
>
> DATE IP_Address
> 2008-06-01 09:00:32.453 192.168.0.1
> 2008-06-07 09:00:21.421 192.168.0.1
> 2008-06-09 09:00:35.997 192.168.0.1
> 2008-06-09 09:01:00.342 192.168.0.1
> 2008-06-09 09:01:32.453 192.168.0.1
> 2008-06-09 09:01:44.421 192.168.0.1
> 2008-06-09 09:10:35.997 192.168.0.1
> 2008-06-09 09:10:55.342 192.168.0.1
>
> Then the result would be
> COUNT DATE IP_Address
> 3 2008-06-09 09:01 192.168.0.1
> 2 2008-06-09 09:10 192.168.0.1
>
>
> Essentially, (note I'm using SQL2005) what you gave in your previous
> example is what I want except preferably specifiying only a count
> greater than 1 (per minute per IP_Address) rather than a "Base Date"
> to start the query from (as I don't want to Admin person to ever not
> see any rows where the count is greater than 1).
>
> Any further would be greatly appreciated.
> Cheers
> Matt