how to retreave date and hour from datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

how to retreave date and hour from datetime

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-10-18 : 14:58:48

Hi,

I need to run some thing like next:

select count(*) , myDateTime
from myTable
where myDateTime > DATEADD(HOUR, -48, sysdatetime())
Group by myDateTime

I need your help on how to get the myDateTime to include date and hour but not minute and second, because the count() needs to group on that. I have tried cast/convert/datediff, but none of thme worked the way I want.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 15:33:48

[code]
select count(*) , DATEADD(hh,DATEDIFF(hh,0,myDateTime),0) AS DateHour
from myTable
where myDateTime > DATEADD(HOUR, -48, sysdatetime())
Group by DATEADD(hh,DATEDIFF(hh,0,myDateTime),0)

[/code]

Also see

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-10-18 : 16:34:09

Wow, that is totaly cool.

Now I have a second part requirement I also have a hard time with.

They want to see a 0 for those hours that no data existed.

Notice in the query, we go back 48 hours. The underlying data may only have say 30 rows, i.e. 2012-10-16 19:00:00:000, 2012-10-16 20:00:00:000 etc are not in the table to begin with.

So I am trying this:
select case when count(*) > 0 then COUNT(*) else 0 end

But it did not give me the 0s. I started to think I need to build a variable table on the fly...

Is there an elegant way without going into var?

I understand some of you folk may say this should be resolved on the app/presentation side. :).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 16:43:05

yep you need to create a table on the fly and use it

like this


;With HourTable (HourDt)
AS
(
SELECT DATEADD(HOUR, DATEDIFF(HOUR,0,sysdatetime())-48,0)
UNION ALL
SELECT DATEADD(HOUR,1,HourDt)
FROM HourTable 
WHERE DATEADD(HOUR,1,HourDt)<=sysdatetime()
)

SELECT HourDt,COALESCE(Cnt,0)
FROM HourTable h
LEFT JOIN (select count(*) AS Cnt, DATEADD(hh,DATEDIFF(hh,0,myDateTime),0) AS DateHour
from myTable 
where myDateTime > DATEADD(HOUR, -48, sysdatetime()) 
Group by DATEADD(hh,DATEDIFF(hh,0,myDateTime),0)
)t
ON t.DateHour = h.HourDt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hommer
Aged Yak Warrior

808 Posts

Posted - 2012-10-18 : 16:57:56

Awesome! That is it! THANK YOU!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 17:02:56

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources