SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Date and time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shajimanjeri
Posting Yak Master

India
179 Posts

Posted - 10/28/2006 :  02:20:45  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 10/28/2006 :  03:40:28  Show Profile  Reply with Quote
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))



KH

Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts

Posted - 10/28/2006 :  06:34:54  Show Profile  Reply with Quote
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))



KH



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 10/28/2006 :  07:08:12  Show Profile  Reply with Quote
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



KH

Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts

Posted - 10/28/2006 :  07:22:35  Show Profile  Reply with Quote

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



KH



Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 10/28/2006 :  07:24:18  Show Profile  Reply with Quote
Sorry. Don't quite get you. Can you post your table structure, some sample data and the result that you want.


KH

Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts

Posted - 10/28/2006 :  07:40:53  Show Profile  Reply with Quote
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



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/28/2006 :  08:01:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 10/28/2006 08:09:16
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 10/28/2006 :  08:22:37  Show Profile  Reply with Quote

			where		mycolumn >= '20061028'
					and mycolumn < '20061029'



KH

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 10/29/2006 :  07:57:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
How true! I must have been in affect when typing...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000