date datetime filtering failed

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 2012 Forums

Transact-SQL (2012)

date datetime filtering failed

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-29 : 01:03:56

Hi Guys,

Am unable to get the desired result using the date and time filtering.

when i tried to filter the records still it show all the records. it should be only

the records that falls from 5:00am to 10:00am. kindly please help me guys what

appropiate commands to filter the dates that includes the time.

Thank you in Advance.

Here is sample data



Create table #sample
(createdDateandTime datetime)
Insert #sample--(createdDateandTime) value ('2013-04-28 16:47:52.000')
Select '2013-04-28 16:47:52.000' union all
select '2013-04-28 21:03:13.000' union all
select '2013-04-29 00:00:48.000' union all
select '2013-04-29 01:40:02.000'
declare @timezoneOffset int
set @timezoneOffset=8
select 
dateadd(hour,@timezoneOffset,createdDateandTime) as CREATEDDATETIME
from #sample
Where createdDateandTime between('2013-04-28 05:00:00') and ('2013-04-29 10:00:00')
Desired Result:
2013-04-29 05:03:13.000
2013-04-29 08:00:48.000
2013-04-29 09:40:02.000

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-29 : 01:17:18

Where Datepart(hour,dateadd(hour,@timezoneOffset,createdDateandTime) ) between 5:00AM and 6:00AM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 01:29:53

does that now work with additional condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-04-29 : 02:08:50

Hi Visak

actually i got an error when compling my SSRS script designer.

ERROR:

Argument data type nvarchar is invalid for argument 2 of dateadd function.

I have an SSRS parameter for @timezoneOffset and the value is 8 int.

declare @timezoneOffset int
set @timezoneOffset=8

select
createdDateandTime AS WITHOUTTIMEZONE,
dateadd(hour,@timezoneOffset,createdDateandTime) as WITHTIMEZONE
from #sample
Where Datepart(hour,dateadd(hour,@timezoneOffset,createdDateandTime) ) between 5 and 9

I need to get the result from the data with timezoneoffset.

WITHOUTTIMEZONE	          WITHTIMEZONE
2013-04-28 21:03:13.000	2013-04-29 05:03:13.000
2013-04-29 00:00:48.000	2013-04-29 08:00:48.000
2013-04-29 01:40:02.000	2013-04-29 09:40:02.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 02:26:05

sounds like you've dateoffset declared as varchar in either report or in sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-05-01 : 22:43:41

Hi VIsakh,

This problem has been fixed. it was delcare as Integer. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-02 : 01:17:50

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources