selecting dates in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

selecting dates in sql

Author

Topic

silentbob
Starting Member

18 Posts

Posted - 2012-11-23 : 04:53:15

Hi

I have the following sql statement that selects data from yesterday between 7 and and 6 (current day)

I am trying to use it for a report in reporting services and I would like to be able to select the date. So for example they would select 1/11/2012 amd 2/11/2012 (thats in UK format) and get back data between 6am on the 1st and 7am on the 2nd.

SELECT readDate as Interval, tagname ,round(value,3) as value, amendedValue
FROM [GasNominations].[dbo].[GasData]
WHERE readDate between 
             dateadd(hour, 7, DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1))   
             and dateadd(hour, 6, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
order by tagname, readdate

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-29 : 06:24:23

Try this


declare @from_date datetime, @to_date datetime

select @from_date ='20121101', @to_date='20121102'
SELECT readDate as Interval, tagname ,round(value,3) as value, amendedValue
FROM [GasNominations].[dbo].[GasData]
WHERE readDate between 
             dateadd(hour, 7, DATEADD(day, DATEDIFF(day, 0, @from_date , -1))   
             and dateadd(hour, 6, DATEADD(day, DATEDIFF(day, 0, @to_date ), 0))
order by tagname, readdate

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources