need help in Stored Procedure (Time Striping)

Question

Post reply

need help in Stored Procedure (Time Striping)

isa-369599

SSCommitted

Points: 1602
More actions
December 15, 2006 at 10:32 pm

#94924

Hello everyone,
i have a table Buyers in the SQL Server 2000, in this table i have the follwing fields
Buyers
BuyerID
BuyerName
LastTransactionDate
RegistrationDate
LastLoginDate
LastPinChange
i want to make a "Hourly Performance Chart", for this i want to make a Stored Procedure, for this, i need time in hourly basis and then i displayed a chart how many buyers are logined in to the system.
e.g i want to see Todays Chart then in this i see in 9.00 am how many buyers logined to system , and at 10 ,11 so on , But if u see the structure of the table i have a REGISTRATION DATE ( in this i have date + time ) then tell me how i strip the time from this field and used in the SP.
plz reply me asap
Thanx in Advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

DaveItz SSCrazy Points: 2146 More actions · Answer 1

There are many ways.

Here are a couple:

declare @MyDateAndTime datetime
select @MyDateAndTime = getdate()
select dateadd(d, 0, datediff(day, 0, @MyDateAndTime))
select convert(datetime, convert(char(8), @MyDateAndTime, 112))

Sorry. I just quickly scanned your post. That will give you the date portion.

If you just want the hour you can do this:

select datepart(hh, @MyDateAndTime)

MohammedU SSCoach Points: 19644 More actions · Answer 2

select cast(LastLoginDate as varchar(11)) as 'date',

datepart(hh, LastLoginDate)as 'hour',

count(1)as #Buyers

from Buyers

Group By cast(LastLoginDateas varchar(11)),

datepart(hh,LastLoginDate)

Order By cast(LastLoginDateas varchar(11)),

datepart(hh,LastLoginDate)

MohammedU
Microsoft SQL Server MVP