September 23, 2014 at 1:02 am
I have a list of data with datetime. I would like to pull the data base on hour.
Example:
datetime IDSample
2014-09-23 00:09:14.297153527576
2014-09-23 00:09:19.903153527579
2014-09-23 00:09:22.250153527583
2014-09-23 00:09:23.747153527585
2014-09-23 08:54:25.393153535349
2014-09-23 08:54:30.800153535353
2014-09-23 11:28:35.427153545958
2014-09-23 11:28:37.203153545961
2014-09-23 11:28:39.170153545963
I would like to schedule the job to run every hour.
Example now is 11:10AM, I only would like to pull data from 11:00 - 11:59
select * from tableA
where datetime between 11:10 and 11:59 and datetime=getdate()
datetime IDSample
2014-09-23 11:28:35.427153545958
2014-09-23 11:28:37.203153545961
2014-09-23 11:28:39.170153545963
How can I apply on where condition to set extrating every hour.
September 23, 2014 at 1:22 am
Here you go:-
SELECT
SampleDate,
CAST(SampleDate AS time),
SampleID
FROM
DateTimeQuery
WHERE
CAST(SampleDate AS time) >= CAST(GETDATE() AS TIME)
AND
CAST(SampleDate AS time) < CAST(DATEADD(hh,1,GETDATE()) AS TIME)
September 23, 2014 at 2:29 am
how to get 1 hour behind?
example current timing 11:28, get 10:00 to 11:00
September 23, 2014 at 2:43 am
SELECT
SampleDate,
CAST(SampleDate AS time),
SampleID
FROM
DateTimeQuery
WHERE
CAST(SampleDate AS time) >= dateadd(hh, datediff(hh, 0, getdate()) - 1, 0)
AND
CAST(SampleDate AS time) < dateadd(hh, datediff(hh, 0, getdate()), 0)
That will return the data from the start of the previous hour to the start of the current hour
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 23, 2014 at 2:46 am
If rounded interval is needed
SELECT
timeStart =
DATEADD(MINUTE
,-DATEPART(MINUTE,getdate())
,DATEADD(SECOND
,-DATEPART(SECOND,getdate())
, DATEADD(MS,
-DATEPART(ms,getdate())
,getdate())))
,timeEnd =
DATEADD(MINUTE
,-DATEPART(MINUTE,getdate()) + 60
,DATEADD(SECOND
,-DATEPART(SECOND,getdate())
, DATEADD(MS,
-DATEPART(ms,getdate()) - 1
,getdate())));
September 23, 2014 at 2:50 am
I get this error:
The data types time and datetime are incompatible in the greater than or equal to operator.
September 23, 2014 at 2:53 am
girl_bj (9/23/2014)
I get this error:The data types time and datetime are incompatible in the greater than or equal to operator.
Which answer are you getting the error on?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 23, 2014 at 2:57 am
For the hour behind use:-
SELECT
SampleDate,
CAST(SampleDate AS time),
SampleID
FROM
DateTimeQuery
WHERE
CAST(SampleDate AS time) >= CAST(DATEADD(hh,-1,GETDATE()) AS TIME)
AND
CAST(SampleDate AS time) < CAST(GETDATE() AS TIME)
Try that and see if you still get the error
September 23, 2014 at 3:27 am
serg-52 (9/23/2014)
If rounded interval is needed
SELECTtimeStart =
DATEADD(MINUTE
,-DATEPART(MINUTE,getdate())
,DATEADD(SECOND
,-DATEPART(SECOND,getdate())
, DATEADD(MS,
-DATEPART(ms,getdate())
,getdate())))
,timeEnd =
DATEADD(MINUTE
,-DATEPART(MINUTE,getdate()) + 60
,DATEADD(SECOND
,-DATEPART(SECOND,getdate())
, DATEADD(MS,
-DATEPART(ms,getdate()) - 1
,getdate())));
Tried this method more accurate.
Is working fine. Thanks all.
September 24, 2014 at 2:51 am
How to overcome the end date issue?
When I try to run it appear together with the minute.
Example current time 08:49:44.6730000
StartDate 07:00:00.0000000
EndDate 08:49:44.6730000
How to make it
StartDate 07:00:00.0000000
EndDate 08:00:00.0000000
Is it possible?
September 24, 2014 at 3:12 am
Do you mean how do you show a rounded time in the end date column?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 24, 2014 at 3:17 am
Yes. Let say the time is 0830.
Would like to get 0700 to 0800
September 24, 2014 at 3:22 am
StartDate = dateadd(hh, datediff(hh, 0, '2014-09-24 08:30:00.000') - 1, 0)
EndDate = dateadd(hh, datediff(hh, 0, '2014-09-24 08:30:00.000'), 0)
Does that do what you want?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
September 24, 2014 at 3:36 am
Im trying to pull it every hour.
The method you provide need to be done manually?
September 24, 2014 at 3:51 am
If you change the fixed date to getdate() or the time you ran the report it will round the hour to the start of that hour for the start date and show the start of the hour previously for the end date. Like so:
select
StartDate = dateadd(hh, datediff(hh, 0, Getdate()) - 1, 0)
,EndDate = dateadd(hh, datediff(hh, 0, getdate()), 0)
EndDate
2014-09-24 10:00:00.000
StartDate
2014-09-24 09:00:00.000
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply