Date and time - SQL Server Forums

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

General SQL Server Forums

Script Library

Date and time

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-10-28 : 02:20:45

Dear all,
I am doing a project on SQL. So I need some help on it...
Here I want to get the records from my database by date and time wise.
So there is one table with a field name Msg_Time (Type is date&time).

Here how to fetch Today's records in between 00:00 to 01:00, 01:00 to 02:00, 02:00 to 03:00, 03:00 to 04:00 etc till 23:00 to 00:00.

Here the storing format of date in my table is like "10/28/2006 12:00:00 AM" and "10/28/2006 11:10:00 PM"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-28 : 03:40:28

for 00:00 to 01:00


where Msg_Time >= dateadd(day, datediff(day, 0, getdate()), 0)
and   Msg_Time <  dateadd(hour, 1, dateadd(day, datediff(day, 0, getdate()), 0))

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-10-28 : 06:34:54

Dear khan,
Please tell me
same way how to find the record from 01:00 to 02:00 and 03:00 to 04:00 etc at the same day.
I am new to SQL queries

please
with regards
shaji

quote:

Originally posted by khtan

for 00:00 to 01:00


where Msg_Time >= dateadd(day, datediff(day, 0, getdate()), 0)
and   Msg_Time <  dateadd(hour, 1, dateadd(day, datediff(day, 0, getdate()), 0))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-28 : 07:08:12

You mean generate the hourly time for a day ?


select	dte = dateadd(hour, hr, dateadd(day, datediff(day, 0, getdate()), 0))
from	(
		select	hr	= 0 union all select 1 union all select 2 union all select 3 union all
		select 4 union all select 5 union all select 6 union all select 7 union all select 8
	) d

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-10-28 : 07:22:35

Dear KH,
No,
There is one table with a field name Msg_Time (Type is datetime).
and the date format is : "10/28/2006 12:40:00 AM"
Here
I want to select Today's record on time based.
I mean In my database how many messages recieved in time between 00:00 to 01:00, 01:00 to 02:00, 03:00 to 04:00, 05:00 to 06:00 etc till 23:00 to 00:00.
How to do this one.
Please help me..

Regards
shaji

quote:

Originally posted by khtan

You mean generate the hourly time for a day ?


select	dte = dateadd(hour, hr, dateadd(day, datediff(day, 0, getdate()), 0))
from	(
		select	hr	= 0 union all select 1 union all select 2 union all select 3 union all
		select 4 union all select 5 union all select 6 union all select 7 union all select 8
	) d

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-28 : 07:24:18

Sorry. Don't quite get you. Can you post your table structure, some sample data and the result that you want.

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-10-28 : 07:40:53

Dear kh,

I have a table with name C1_Messages with three fields (1)ID (2) Messages (3)Msg_Time.
Here the end users are sending messages and storing those messages in this table with today's date and time. (once the message is recieved then automatically server date and time will store).
Here I am getting 2-20 messages per hour.
So here I want to see how many messages recieved on today in between 00:00 to 01:00,and in between 01:00 to 02:00 and in between 02:00 to 03:00, etc till and in between 23:00 to 00:00 (The time are in 24-hour format).

How to do that

quote:
Originally posted by khtan

Sorry. Don't quite get you. Can you post your table structure, some sample data and the result that you want.

KH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-28 : 08:01:38

Please also post in appropriate forum. The Script Library is for posting working script

select		dateadd(hour, 0, x.hd) FromDateHour,
		dateadd(second, -1, dateadd(hour, -1, x.hd)) ToDateHour,
		x.cnt Messages
FROM		(
			select		datediff(hour, 0, Msg_Time) hd,
					count(*) cnt
			from		mytable
			where		mycolumn >= '20061028'
					and mycolumn < '20061028'
			GROUP BY	DATEDIFF(hour, 0, Msg_Time) / 24
		) x

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-10-28 : 08:22:37

[code]
where mycolumn >= '20061028'
and mycolumn < '20061029'
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-29 : 07:57:53

How true! I must have been in affect when typing...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources