February 16, 2011 at 3:23 pm
Hi everybody!
I'm new to MSSQL and already I get headache...
I have file name DATE and its datetime.
I need to get all records from last hour. How to do that?
Thanks!
February 16, 2011 at 3:32 pm
WHERE [DATE] >= DATEADD(HOUR, - 1, GETDATE())
Andreas Goldman
February 16, 2011 at 4:01 pm
Andreas Goldman (2/16/2011)
WHERE [DATE] >= DATEADD(HOUR, - 1, GETDATE())
hm... this query gets all data from table. My last record was inserted 08/02/2011 11:44:56 PM so
it must return zero rows because today i havent any new records in table.
February 16, 2011 at 4:16 pm
Jole84 (2/16/2011)
Andreas Goldman (2/16/2011)
WHERE [DATE] >= DATEADD(HOUR, - 1, GETDATE())hm... this query gets all data from table. My last record was inserted 08/02/2011 11:44:56 PM so
it must return zero rows because today i havent any new records in table.
Can you provide the table ddl and some sample data?
What Andreas posted (as long as you swap [DATE] for your actual datefield) should have worked, if it's a standard datetime field.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2011 at 4:27 pm
CREATE TABLE dbo.cData (
ID bigint NOT NULL IDENTITY(1, 1),
[Value] float,
[Date] datetime
)
in Date field i have stored record like: 08/02/2011 09:55:50 PM
February 16, 2011 at 4:34 pm
and we haven't gotten to 8/2/2011 yet (I read it as 2010 for some reason) so yes, it's after an hour ago... LOL
Check out this script, it should show you what's happening. Please note how I set up the data to be consumable for testing, you'll want to do this in the future if you have other questions to make sure you can get tested code back.
IF OBJECT_ID('tempdb..#cData') IS NOT NULL
DROP TABLE #cData
CREATE TABLE dbo.#cData (
ID bigint NOT NULL IDENTITY(1, 1),
cValue float,
cDate datetime
)
DECLARE @recentdate DATETIME
SET @recentdate = DATEADD( n, -5, getdate())
INSERT INTO #cData (cValue, cDate) VALUES ( 87234.12312, '08/02/2011 09:55:50 PM')
INSERT INTO #cData (cValue, cDate) VALUES ( 87234.12312, '08/02/2010 09:55:50 PM')
INSERT INTO #cData (cValue, cDate) VALUES ( 87234.12312, @recentdate)
select * from #cdata
select * from #cdata
where cDate >= DATEADD( hour, -1, getdate())
select * from #cdata
where cDate >= DATEADD( hour, -1, getdate())
AND cDate <= GETDATE()
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 1:53 pm
Thank you Craig, it works when i use:
select * from #cdata where cDate >= DATEADD( hour, -1, getdate())
AND cDate <= GETDATE()
but if I modify that to show data from 2 weeks ago it show me only data from today. I have change hour to week and put -2.
Am I doing something wrong?
February 17, 2011 at 1:55 pm
Jole84 (2/17/2011)
Thank you Craig, it works when i use:
select * from #cdata where cDate >= DATEADD( hour, -1, getdate())AND cDate <= GETDATE()
but if I modify that to show data from 2 weeks ago it show me only data from today. I have change hour to week and put -2.
Am I doing something wrong?
Weeks work by weeknumber based off the calendar days, it's not a straight 14 day count. Instead of wk, -2, try using dd, -14.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 2:19 pm
Craig Farrell (2/17/2011)
Weeks work by weeknumber based off the calendar days, it's not a straight 14 day count. Instead of wk, -2, try using dd, -14.
Yes, I try to do that also, before I posted here, but it shows me only records from last day.
I dont know what is wrong :unsure:
February 17, 2011 at 2:28 pm
Jole84 (2/17/2011)
Craig Farrell (2/17/2011)
Weeks work by weeknumber based off the calendar days, it's not a straight 14 day count. Instead of wk, -2, try using dd, -14.
Yes, I try to do that also, before I posted here, but it shows me only records from last day.
I dont know what is wrong :unsure:
If you're using my sample data/build, there's no records in the last 14 days then there was in the last hour, so that's correct.
If you're using your real data and you're SURE there's records in the last 14 days, post the actual sql you're using, we'll try to help you clean it up for what you want.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 3:07 pm
Craig Farrell (2/17/2011)
If you're using my sample data/build, there's no records in the last 14 days then there was in the last hour, so that's correct.
If you're using your real data and you're SURE there's records in the last 14 days, post the actual sql you're using, we'll try to help you clean it up for what you want.
I have my own data, first record was inserted 02/07/2011 11:44:11 PM
February 17, 2011 at 3:28 pm
Jole84 (2/17/2011)
Craig Farrell (2/17/2011)
If you're using my sample data/build, there's no records in the last 14 days then there was in the last hour, so that's correct.
If you're using your real data and you're SURE there's records in the last 14 days, post the actual sql you're using, we'll try to help you clean it up for what you want.
I have my own data, first record was inserted 02/07/2011 11:44:11 PM
Then please post your actual SQL, as I requested, and we might be able to help.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 3:31 pm
Craig Farrell (2/17/2011)
I have my own data, first record was inserted 02/07/2011 11:44:11 PM
Then please post your actual SQL, as I requested, and we might be able to help.[/quote]
select * from dbo.myData
where date >= dateadd(day, -14, getdate()) AND date <= GETDATE()
February 17, 2011 at 3:40 pm
Can you run this in a separate query window and tell me if you get one, or two, records back?
IF OBJECT_ID('tempdb..#MyData') IS NOT NULL
DROP TABLE #MyData
CREATE TABLE #MyData
([Date] DATETIME)
INSERT INTO #MyData VALUES ( '20110207 11:44:11PM')
INSERT INTO #MyData VALUES ( '02/07/2011 11:44:11PM')
SELECT * FROM #MyData WHERE date >= DATEADD(day, -14, getdate()) AND date <= GETDATE()
I'm wondering if you're dealing with a language date display problem here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 4:03 pm
Craig Farrell (2/17/2011)
Can you run this in a separate query window and tell me if you get one, or two, records back?
IF OBJECT_ID('tempdb..#MyData') IS NOT NULL
DROP TABLE #MyData
CREATE TABLE #MyData
([Date] DATETIME)
INSERT INTO #MyData VALUES ( '20110207 11:44:11PM')
INSERT INTO #MyData VALUES ( '02/07/2011 11:44:11PM')
SELECT * FROM #MyData WHERE date >= DATEADD(day, -14, getdate()) AND date <= GETDATE()
I'm wondering if you're dealing with a language date display problem here.
I get two records back.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply