No records found in my Query - 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)

No records found in my Query

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-05-28 : 02:49:38

Hi Guys,

I have a table with datetime datatype.
i have to pull out records via date and hours.
the hours should be 6:00AM from the previous date to 6:00AM in the current date. My query doesnt pullout the records that i indicate the date and the hours in the where clause. kindly please help me guys any solution on my scripts. i did not get the '2012/5/27'
My Query
[Code]
DECLARE @fromDateTime as datetime,
@toDateTime as datetime
SET @fromdatetime = dateadd(day,datediff(day,0,'2012/05/27'),'6:00')-1
SET @todatetime = dateadd(day,datediff(day,0,'2012/05/27'),'6:00')

DECLARE @SAMPLE TABLE (CreatedDatetime Datetime)

INSERT @SAMPLE (CreatedDatetime)
VALUES
('2012-05-27 02:41:38.000'),
('2012-05-27 02:43:06.000'),
('2012-05-27 02:46:26.000'),
('2012-05-27 02:48:44.000'),
('2012-05-26 05:51:07.000'),
('2012-05-26 05:52:34.000'),
('2012-05-26 05:53:43.000')

Select * from @SAMPLE
WHERE createddatetime BETWEEN @fromDateTime and @toDateTime

Derived Result: I should have 4 records from '2012/05/27'
[/Code]

Thanks,

JOV

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-28 : 02:55:22

you don't have any records that falls between these 2 date
2012-05-27 06:00
2012-05-28 06:00

Add this to your query to display your from and to datetime


select	@fromDateTime, @toDateTime

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 02:57:49

for getting that output you from date should be

SET @fromdatetime = dateadd(day,datediff(day,0,'2012/05/28'),0)-1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-05-28 : 03:04:50

Sorry guys,

These are the correct parameter value.

SET @fromdatetime = dateadd(day,datediff(day,0,'2012/05/27'),'6:00')-1
SET @todatetime = dateadd(day,datediff(day,0,'2012/05/27'),'6:00')

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-05-28 : 03:12:29

@Kthan, you mean, i need to add additional 0 zero from the Hour.

@Visakh16, i need to add the Hrou to validate the transaction date. the report process every 06:00 am of the previous date and 06:00 am of the current date.

Thank you guys for the reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-28 : 03:19:19

quote:
Originally posted by Villanuev

Sorry guys,

These are the correct parameter value.

SET @fromdatetime = dateadd(day,datediff(day,0,'2012/05/27'),'6:00')-1
SET @todatetime = dateadd(day,datediff(day,0,'2012/05/27'),'6:00')

if these are the values, you will get the 4 records.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-28 : 03:21:35

quote:
Originally posted by Villanuev

@Kthan, you mean, i need to add additional 0 zero from the Hour.

Thank you guys for the reply.

No. Your initial query specify the date as 2012/05/28

SET @fromDateTime = dateadd(day,datediff(day,0,'2012/05/28'),'6:00')-1
SET @toDateTime = dateadd(day,datediff(day,0,'2012/05/28'),'6:00')

this will result in @fromDateTime & @toDateTime of
2012-05-27 06:00 & 2012-05-28 06:00

and that will not match any records

KH
[spoiler]Time is always against us[/spoiler]

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-05-28 : 03:27:41

Okay. Thanks for clarification.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-05-28 : 05:07:43

@Khtan & @Visakh,

What is timezoneOffset? I think this is the reason why i did not get the correct records based on the where clause(date).
I notice that some of the query have this in the statement..

dateadd(hour,convert(int,@timezoneOffset),p.createddatetime)

btw, how to get the timezoneoffset?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:47:32

quote:
Originally posted by Villanuev

@Khtan & @Visakh,

What is timezoneOffset? I think this is the reason why i did not get the correct records based on the where clause(date).
I notice that some of the query have this in the statement..

dateadd(hour,convert(int,@timezoneOffset),p.createddatetime)

btw, how to get the timezoneoffset?

timezone offset is time in hours by which your datetime value differs from a reference datetime value (usually its based on GMT datetime value)
seeing your query it looks like @timezoneOffset value comes from parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-05-28 : 21:10:22

Nice one Visakh.
Yes, it was stored in a parameter.

Ex.
Our server uses the US timezone and we convert it to GMT+8.
Meaning the @timezoneOffset parameter has a value of +8 or 8.
So, all the CreatedDatetime, if i will use the @timezoneOffset
I will have additional 8 hrs.

Example:

Declare @timezoneOffset INT
Set @timezoneOffset =8
Select dateadd(hour,convert(int,@timezoneOffset),GETDATE())

Current time :2012-05-29 09:04:59.407
Use timezoneOffset : 2012-05-29 17:05:13.230

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 01:29:06

Ok...that makes sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources