Remove minutes from convert(varchar,[DateTime]) 3

[tqeonline]

I have a query that returns the average service time within an hour. But i can't get it to group by the hour because the minutes are being returned.

The code that I currently have is:

Select convert(varchar,[DateTime]),
SUM(CASE WHEN Status = '1' THEN 1 ELSE 0 END)
/cast(count([Status]) as float)*100 as Uptime
FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] NoLock
WHERE [DateTime] >= DATEADD(hour,-24,GETDATE())
Group By convert(varchar,[DateTime])
order by cast(convert(varchar,[DateTime]) as datetime) desc

The issue is it returns it like:

Sep 9 2010 1:41PM | 25
Sep 9 2010 1:40PM | 75

but it should be

Sep 9 2010 1PM | 50

I have been messing with TRIM and CAST and can't figure out how to remove the minutes from the result to get it to group the averages by the HOUR.

Any ideas?

 

[Chopstik]

The following may be one option, though no promises that it's the best option. You can change it as needed.

SELECT GETDATE(), CONVERT(VARCHAR(50),GETDATE(),107), DATEPART(hh,GETDATE()),
CASE WHEN DATEPART(hh,GETDATE()) > 12 THEN CONVERT(VARCHAR(5),DATEPART(hh,GETDATE())-12) + ' PM'
ELSE CONVERT(varchar(5),DATEPART(hh,GETDATE())) + ' AM'
END

------------------------------------------------------------------------------------------------------------------------
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

http://lessthandot.com

 

[gmmastros]

Truncating your date/time values to remove minutes is pretty simple, once you know how. Sure... you can mess around with a bunch of converts and strings, but there is an easier way. Take a look at this:

Select DateAdd(Hour, DateDiff(Hour, 0, GetDate()), 0)

Let's take a close look at how this works. First we do a date diff. The unit is Hour. We are actually getting the difference in hours between 0 (Jan 1, 1900) and GetDate() (current date and time). DateDiff returns an integer, so the minutes will be truncated off. Next, we add that number of hours back to 0 (Jan 1, 1900). This effectively removes the the minutes.

Applying this to your query...

Select DateAdd(Hour, DateDiff(Hour, 0, [DateTime]), 0) As [MyDateTime],
       SUM(CASE WHEN Status = '1' THEN 1 ELSE 0 END)
       /cast(count([Status]) as float)*100 as Uptime
FROM   [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] NoLock
WHERE  [DateTime] >= DATEADD(hour,-24,GETDATE())
Group By DateAdd(Hour, DateDiff(Hour, 0, [DateTime]), 0)
order by DateAdd(Hour, DateDiff(Hour, 0, [DateTime]), 0) desc

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

George:

I always learn something from you.

Because you take the time to explain, rather than just give an answer.

Have a *.


Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[TheBugSlayer]

That' s a neat trick G.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[tqeonline]

They both work.. however George your example keeps the place holder of the actual seconds 1/1/2010 12:00:00 etc

I used Chopstiks version however had to critique it to my needs... I seriously think this could be dramatically reduced but it returns in like .002 so it works great.

            SELECT
            CONVERT(VARCHAR(50),[Datetime],101),
            CASE WHEN DATEPART(hh,[DateTime]) > 12 
            THEN CONVERT(VARCHAR(5),DATEPART(hh,[DateTime])-12) + ' PM'
            ELSE CONVERT(varchar(5),DATEPART(hh,[DateTime])) + ' AM'END as Date,

            SUM(CASE WHEN Status = '1' THEN 1 ELSE 0 END)/cast(count([Status]) as float)*100 as Green,
            SUM(CASE WHEN Status = '2' THEN 1 ELSE 0 END)/cast(count([Status]) as float)*100 as Yellow,
            SUM(CASE WHEN Status = '3' THEN 1 ELSE 0 END)/cast(count([Status]) as float)*100 as Red

            FROM [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] NoLock

            Group by CONVERT(VARCHAR(50),[Datetime],101),
            CASE WHEN DATEPART(hh,[DateTime]) > 12 
            THEN CONVERT(VARCHAR(5),DATEPART(hh,[DateTime])-12) + ' PM'
            ELSE CONVERT(varchar(5),DATEPART(hh,[DateTime])) + ' AM'END

            order by cast(CONVERT(VARCHAR(50),[Datetime],101) as datetime),
            CONVERT(VARCHAR(50),[Datetime],101),
            CASE WHEN DATEPART(hh,[DateTime]) > 12 
            THEN CONVERT(VARCHAR(5),DATEPART(hh,[DateTime])-12) + ' PM'
            ELSE CONVERT(varchar(5),DATEPART(hh,[DateTime])) + ' AM'END
[code]

this displays 5 columns

1/2/2010 | 3 pm | 25% | 25% | 50%

 

[gmmastros]

That's because my version actually returns a DateTime (with minutes and seconds truncated). If this was a query in my database, I would have done it *my* way and returned 4 columns instead of 5 and then done the formatting the date and hour on the front end (presumably a report).

To each their own. I'm not criticizing your choice, just explaining what I would have done.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Very similar problem (slightly different though) is discussed in this blog (problem #1)
Interesting T-SQL problems

PluralSight Learning Library

 

[tqeonline]

Ok ok... i must confess after working on the original answer, I found it easier to switch to yours.

Here is the end result with Drop down references:

Select DateAdd(Hour, DateDiff(Hour, 0, [DateTime]), 0) As Date,    
            SUM(CASE WHEN Status = '1' THEN 1 ELSE 0 END)      
             /cast(count([Status]) as float)*100 as Up,

            SUM(CASE WHEN Status = '2' THEN 1 ELSE 0 END)      
             /cast(count([Status]) as float)*100 as Slow,

            SUM(CASE WHEN Status = '3' THEN 1 ELSE 0 END)      
             /cast(count([Status]) as float)*100 as Down

            FROM   [default_bsd_qa_db].[dbo].[SQA_DASH_SYNT_TRAN_LOG] NoLock
            WHERE  [DateTime] >= DATEADD(hour,-24,GETDATE())
            AND [Type] LIKE @TYPE
            AND [Environment] LIKE @Environment
            AND [Location] LIKE @Location
            AND [Page] LIKE @Page
            Group By DateAdd(Hour, DateDiff(Hour, 0, [DateTime]), 0)
            order by DateAdd(Hour, DateDiff(Hour, 0, [DateTime]), 0) desc">