| Author |
 Topic  |
|
|
Villanuev
Constraint Violating Yak Guru
309 Posts |
 Posted - 05/28/2012 : 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 |
Edited by - Villanuev on 05/28/2012 03:03:10
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 05/28/2012 : 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
Time is always against us
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47034 Posts |
Posted - 05/28/2012 : 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
309 Posts |
Posted - 05/28/2012 : 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')
|
Edited by - Villanuev on 05/28/2012 03:05:31 |
|
|
|
Villanuev
Constraint Violating Yak Guru
309 Posts |
Posted - 05/28/2012 : 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)
Singapore
16745 Posts |
Posted - 05/28/2012 : 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
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 05/28/2012 : 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
Time is always against us
|
|
|
|
Villanuev
Constraint Violating Yak Guru
309 Posts |
Posted - 05/28/2012 : 03:27:41
|
| Okay. Thanks for clarification. |
|
|
|
Villanuev
Constraint Violating Yak Guru
309 Posts |
Posted - 05/28/2012 : 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? |
Edited by - Villanuev on 05/28/2012 05:25:58 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47034 Posts |
Posted - 05/28/2012 : 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
309 Posts |
Posted - 05/28/2012 : 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
India
47034 Posts |
Posted - 05/29/2012 : 01:29:06
|
Ok...that makes sense
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
No records found in my Query
