SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 date datetime filtering failed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 04/29/2013 :  01:03:56  Show Profile  Reply with Quote
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

441 Posts

Posted - 04/29/2013 :  01:17:18  Show Profile  Reply with Quote

Where Datepart(hour,dateadd(hour,@timezoneOffset,createdDateandTime) ) between 5:00AM and 6:00AM
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  01:29:53  Show Profile  Reply with Quote
does that now work with additional condition?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 04/29/2013 :  02:08:50  Show Profile  Reply with Quote
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



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/29/2013 :  02:26:05  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 05/01/2013 :  22:43:41  Show Profile  Reply with Quote
Hi VIsakh,

This problem has been fixed. it was delcare as Integer. thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/02/2013 :  01:17:50  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000