March 11, 2011 at 7:49 pm
Sir/Madam, i am in a confuse mood. i could not able to retrieve data from database based on current date and time.
i have used GETDATE() function but still no result. probably i could not able to use properly the GETDATE() function.
i need a sql query to retrieve data based on current date and time. i have a table with a column of DateTime whose datatype is datetime. i have to check for the current date and only the current hour, means no need to check for the minutes, seconds. As soon as the time changes i need to retrieve the current data according to time and date.
For example if time is 8:00:00, i need to retrieve 8 o'clock data and it remain same till 8:59:59 and as it changes to 9:00:00 i need to retrieve 9 o'clock data and it must remain same till 9:59:59. Is it possible? plz, provide me some useful suggestion so that i can continue further.
thank u in advanced. Any small help will highly be appreciated....
March 11, 2011 at 8:44 pm
sumpritpurabi (3/11/2011)
Sir/Madam, i am in a confuse mood. i could not able to retrieve data from database based on current date and time.i have used GETDATE() function but still no result. probably i could not able to use properly the GETDATE() function.
i need a sql query to retrieve data based on current date and time. i have a table with a column of DateTime whose datatype is datetime. i have to check for the current date and only the current hour, means no need to check for the minutes, seconds. As soon as the time changes i need to retrieve the current data according to time and date.
For example if time is 8:00:00, i need to retrieve 8 o'clock data and it remain same till 8:59:59 and as it changes to 9:00:00 i need to retrieve 9 o'clock data and it must remain same till 9:59:59. Is it possible? plz, provide me some useful suggestion so that i can continue further.
thank u in advanced. Any small help will highly be appreciated....
-- See how you start off by actually creating a table and then
-- inserting the data into it? Your doing this makes it a lot easier
-- for all of us volunteers to help you. So, help us help you.
-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- for more details on how to do all of this.
DECLARE @test TABLE (MyDate datetime);
-- build a virtual tally table to use in making some test data
;WITH
TENS (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1 FROM TENS t1 CROSS JOIN TENS t2 CROSS JOIN TENS t3),
MILLIONS (N) AS (SELECT 1 FROM THOUSANDS t1 CROSS JOIN THOUSANDS t2),
TALLY (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM MILLIONS)
-- Get the number of hours between day "0" (01/01/1900) and the current date/time.
-- Add these hours back to day "0" (effectively stripping off minutes/seconds/ms).
-- Then add one hour for the ending time.
DECLARE @StartDate datetime,
@EndDate datetime;
SET @StartDate = DateAdd(hour, DateDiff(hour, 0, GetDate()), 0);
SET @EndDate = DateAdd(hour, 1, @StartDate);
INSERT INTO @test -- add to the test data
-- get 10000 rows where each entry is one second earlier than the previous entry
SELECT TOP 10000 DateAdd(second, -N, GetDate())
FROM TALLY
UNION ALL
-- get 10000 rows where each entry is one second later than the previous entry
SELECT TOP 10000 DateAdd(second, N, GetDate())
FROM TALLY
-- now, get data that is for this hour:
SELECT MyDate
FROM @test
WHERE MyDate >= @StartDate
AND MyDate < @EndDate -- must use "<", not "<=" to work properly!
-- a BETWEEN is implicitly converted to <=, so you can't use BETWEEN either.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2011 at 9:48 pm
select
*
from
MyTable
where
-- Datetime greater than or equal to start of current hour
MyDatetime >= dateadd(hh,datediff(hh,0,getdate()),0)and
-- Datetime less than start of next hour
MyDatetime < dateadd(hh,datediff(hh,0,getdate())+1,0)
March 11, 2011 at 11:58 pm
Thank u sir, your query is an immense help for me. Now i can proceed further. But sir i want to ask you one more question. can u just tell me how i can add 3 hours to that query.
i have used SELECT GETDATE()+'03:00:00', but i do not get the result. i want the same as above query which you have given but to add 3 hours more inorder that i can fetch the datas of the next 3 hours.
Thank u once again sir. Thank u very much.
March 12, 2011 at 12:37 am
sumpritpurabi (3/11/2011)
Thank u sir, your query is an immense help for me. Now i can proceed further. But sir i want to ask you one more question. can u just tell me how i can add 3 hours to that query.i have used SELECT GETDATE()+'03:00:00', but i do not get the result. i want the same as above query which you have given but to add 3 hours more inorder that i can fetch the datas of the next 3 hours.
Thank u once again sir. Thank u very much.
I'm not sure which code you're referring to, but in the one I did, just change
SET @EndDate = DateAdd(hour, 1, @StartDate);
to
SET @EndDate = DateAdd(hour, 4, @StartDate);
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 12, 2011 at 11:08 pm
Thank you Sir. I got it. I have just added this line to the query which you gave and i found the result
select * from tablename where DateTime >= dateadd(hh,datediff(hh,0,getdate()+'03:00:00'),0) and DateTime < dateadd(hh,datediff(hh,0,getdate()+'03:00:00')+1,0)
Thank you sir. Itz only because of you i can continue with my work. Thank u sir
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply