score:8
Here's a slightly simpler way:
DECLARE @d SMALLDATETIME;
SELECT @d = DATEADD(HOUR, DATEDIFF(HOUR, '20000101', GETDATE()) - 1, '20000101');
SELECT @d;
Now you can use @d
in your query, e.g.
WHERE col >= @d AND col < DATEADD(HOUR, 1, @d);
This is an open-ended date range. Please don't think about the "end" of the range or consider this a BETWEEN
query. BETWEEN 10:00 AND 10:59
is not a very wise approach, because you may miss data from 10:59:00.003 -> 10:59:59.997
. Background info on why BETWEEN
is evil.
Read More
- SQL Date Range only show data for the last hour
- SQL query to get data that only show the latest date from tables
- How do I only show data from the last seven days or if the date is null?
- SQL Server: Get data for only the past year
- SQL Server: How to select all days in a date range even if no data exists for some days
- SQL - Select records for the same day between particular data range
score:1
Try this one :
declare @lowerRange datetime =
dateadd(hh,datepart(hour,dateadd(hh,-1,getdate())) ,
cast(cast(getdate() as date) as smalldatetime) )
declare @upperRange datetime = dateadd(hour,1,@lowerRange)
select * from yourtable where yourdate between @lowerRange and @upperRange
score:1
This returns an hour range, of the previous hour to now; meaning that if it was 11:35AM
, it would return 10 AM to 11 AM
:
DECLARE @today DATETIME, @hour DATETIME, @hourtwo DATETIME
SET @today = GETDATE()
-- Test other times
--SET @today = '2013-11-04 11:37.22'
SELECT @hour = DATEADD(hh,-2,@today)
SELECT @hourtwo = DATEADD(hh,-1,@today)
SELECT CONVERT(SMALLDATETIME,ROUND(CAST(@hour as float) * (24/1),0)/(24/1)) AS PreviousHourBegin
SELECT CONVERT(SMALLDATETIME,ROUND(CAST(@hourtwo as float) * (24/1),0)/(24/1)) AS PreviousHourEnd
score:1
This should work:
DECLARE @D DATETIME
SET @D = GETDATE()
SELECT @D AS 'Date',
DATEADD(HOUR,-1,DATEADD(MINUTE,-(DATEPART(MINUTE, @D)),DATEADD(SECOND,-(DATEPART(SECOND, @D)),DATEADD(MILLISECOND,-(DATEPART(MILLISECOND, @D)),@D)))) AS 'Range start',
DATEADD(MINUTE,-(DATEPART(MINUTE, @D)),DATEADD(SECOND,-(DATEPART(SECOND, @D)),DATEADD(MILLISECOND,-(DATEPART(MILLISECOND, @D)),@D))) AS 'Range end'
For the date:
2013-11-04 17:35:51.843
This will return a range like:
Start: 2013-11-04 16:00:00.000
End: 2013-11-04 17:00:00.000
For times between 00:00:00-01:00:00 it will get the range 23:00:00-00:00:00 from the previous day.
score:1
declare @today datetime
set @today=GETDATE()
select @today, DATEADD(HOUR, -2, DATEADD(HOUR, DATEDIFF(HOUR, 0, @today), 0)), DATEADD(MINUTE, -1, DATEADD(HOUR, -1, DATEADD(HOUR, DATEDIFF(HOUR, 0, @today), 0)))
result:
2013-11-04 17:42:17.933 2013-11-04 15:00:00.000 2013-11-04 15:59:00.000
More questions
- SQL Server Pivots... Can I show the last five orders for a series of order types
- SQL How to show '0' value for a month, if no data exists in the table for that month
- SQL - Search table for last day of each month in date range and output data?
- Microsoft SQL Server : return only the rows with the most recent date for each unique ID
- SQL show for every customers only last invoice number from same table
- Show the total sale in Hourly basis for a date range
- SQL Server: Getting the last records for a given set of components that occured before a specific date
- SQL Query to fetch data based on the date range
- SQL : Get last 3 month data (with only available data in the column), not from current month or today?
- SQL Query for extracting rate and date started and ended for the table and data shown
- How to get the last month's data for a quarter in SQL Server?
- SQL Hourly Data Calculations for a date range
- How to query in SQL Server for data in particular date range
- How to extract data between a date range which has only start and end date values in the table?
- Delete all data except for the last 2 days using SQL
- How do I find the data directory for a SQL Server instance?
- What is the most appropriate data type for storing an IP address in SQL server?
- SQL get the last date time record
- SQL query for getting data for last 3 months
- Taking the second last row with only one select in SQL Server?
- SQL Server 2016 - How to get last logged in date for user?
- SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
- SQL Server 2005 Get First and Last date for any Month in any Year
- How to get the last month data and month to date data
More questions with similar tag
- For each day between two dates, add a row with the same info but only that day in the start/end columns
- Extract Certain String after Some Expression
- How to copy some data from one table to another?
- Stored Procedure doesn't return records when called from C#, but works in SSMS
- DateTimeOffset value changing
- How to efficiently prune data
- Decimal with high precision and scale creating 'Arithmetic Overflow Error'
- how to get the list of users accessing to particular tables or views
- Oracle DB Table to SQL Server DB Table
- OPENXML, Xsi:nil and Decimals
- SQL: SELECT Statement to Return All Records, JOIN Value Is NULL, Query Returns No Records
- Generating a Date Span from a Table with Monthly Entries
- How to replace (null) values with 0 output in PIVOT
- How to pull week ending dates and end of month when end of month is mid week
- Tool to visualize SQL Cascade tree
- sql query search for vertical saving
- Not to add delimiter after last column while exporting SQL Server data to txt file - c#
- How to find which application changes a table
- what type of data type use for year and month?
- Microsoft Dynamics CRM or SQL Server?
- Is there a way to run a MS Access query in the background?
- Bad Dates: SQL Server Offers ISDATE(), But Informix Does Not. Is There an Equal Workaround?
- Convert negative data into positive data in SQL Server
- How Do You Monitor The CPU Utilization of a Process Using PowerShell?
- row with minimum value of a column
- Making parameter Optional with multivalue parameter in SSRS?
- What is the best way to get identity of records inserted using DBIx::Class::ResultSet::populate()?
- Sql Server Performance Issue with link and join
- How do I connect & administer an SQL Server database remotely over the Internet?
- Grouping the rows of a table in sql server