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-2 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-2 -- 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-2
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy