Stored Procedure datetime parameter

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

Transact-SQL (2005)

Stored Procedure datetime parameter

Author

Topic

nonsec
Starting Member

26 Posts

Posted - 2009-07-10 : 09:54:15

Hello everyone,

I have stored procedure and I define datetime parameters and there is +4 hours difference between UTC and local system time. In my stored procedure I am moving daily data from one db to another db. My stored procedure scheduled to run at 02:30 AM every morning.

Here is my stored procedure;

ALTER PROCEDURE [dbo].[Usp_CopySCOMNworksPerfDataDailytoPerfData2DB]
AS
Declare @StartDate Datetime,
        @EndDate Datetime 
    

set @StartDate=GETDATE()-1
set @EndDate=GETDATE()

set @StartDate=dateadd(HH,20,convert(Varchar(10),@StartDate,101))
set @EndDate=Dateadd(SS,-1,DATEADD(HH,20,Convert(Varchar(10),@EndDate,101)))
select @Startdate as Startdate,  @EndDate as Enddate


Insert Into "PerfData2".dbo.VM5MinuteData (DateTime,Path,Objectname,Countername,SampleValue)
SELECT     Perf.vPerfRaw.DateTime, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName, 
                      Perf.vPerfRaw.SampleValue
FROM         Perf.vPerfRaw INNER JOIN
                      vPerformanceRuleInstance ON 
                      Perf.vPerfRaw.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN
                      vManagedEntity ON Perf.vPerfRaw.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
                      vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE     	((vPerformanceRule.CounterName = 'cpuUsedPct' and vPerformanceRule.ObjectName = 'VMGuest-cpu') or 
			(vPerformanceRule.CounterName = 'memoryActivePct' and vPerformanceRule.ObjectName = 'VMGuest-memory')) and
					  (Perf.vPerfRaw.DateTime between @Startdate and @Enddate) 
ORDER BY	vManagedEntity.Path, Perf.vPerfRaw.DateTime

I want to move the data daily for example; for example just '2009-07-08 00:00:00.00' to '2009-07-08 11:59:59.00' that is full one day in stored procedure, in order to get full one day data I defined

Startdate: 2009-07-07 8:00:00.00 + 4 hours = 2009-07-08 00:00:00.00
E nd Date: 2009-07-08 7:59:59.00 + 4 hours = 2009-07-08 11:59:59.00

my first question is;

Stored procedure runs every morning 02:30 AM and when I look at the destination database ("PerfData2") I see last last record datetime 6:30 ahead then I expected. How can I fix this?

Second Question is;
I am not sure if this is good approach or not? What is best practise for this? How should I edit my datatime parameter section?

I really appreciate any input;

Thank you.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 13:49:51

>>I see last last record datetime 6:30 ahead then I expected. How can I fix this?
Are you saying that some of the resulting rows had [DateTime] values beyond the limits of your date range (@StartDate,@EndDate)?

>>What is best practise for this?
Not really sure what "this" is. Is it that you are trying to define a "day" based on as 12 hours UTC timespan of midnight to 12:00 noon even though all your data is based on local time?

Also, shouldn't your "day" be 24 hours rather than 12 hours?

Be One with the Optimizer
TG

nonsec
Starting Member

26 Posts

Posted - 2009-07-10 : 14:08:24

TG,
1- Yes records [DateTime] values beyond the limits of your date range.

2- in the stored procedure it is 24 hours. Please look at the below.

Declare @StartDate Datetime,
@EndDate Datetime

set @StartDate=GETDATE()-1
set @EndDate=GETDATE()

set @StartDate=dateadd(HH,20,convert(Varchar(10),@StartDate,101))
set @EndDate=Dateadd(SS,-1,DATEADD(HH,20,Convert(Varchar(10),@EndDate,101)))
select @Startdate as Startdate, @EndDate as Enddate

All I need is getting prior day data in utc time.

thank you.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-10 : 14:26:39

Why are you converting the date to a VARCAHR then doing a DATEADD on a varchar value?

How does teh UTC offset factor in to your query?

If you want the get all the data for the previos day try this:

Declare @StartDate Datetime,
@EndDate Datetime 

SET @StartDate = DATEADD(D, DATEDIFF(D, 0, DATEADD(D, -1, CURRENT_TIMESTAMP)), 0)
SET @EndDate = DATEADD(D, 1, @StartDate)

SELECT *
FROM MyTable
WHERE MyDate >= @StartDate
AND MyDate < @EndDate

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 14:35:06

sorry I misread the results originally - I guess this is a 12 hour AM/PM time rather than 24 hour time:

quote:

Startdate: 2009-07-07 8:00:00.00 + 4 hours = 2009-07-08 00:00:00.00
E nd Date: 2009-07-08 7:59:59.00 + 4 hours = 2009-07-08 11:59:59.00

But I don't see any problem with your WHERE clause. Can you just run the SELECT statement without the INSERT and see that you are getting rows outside the range of whatever your @startDate and @endDate parameters resolve to?

this is the way I would have done it but I think we get the same results for @start and @end dates, right? I find it easier to make the endDate 00:00:00.000 of the next day and then say < @endDate


declare @s datetime
       ,@e datetime
       ,@UTC_Offset int

set @utc_offset = datediff(hour, getutcdate(), getdate())

--daterange for previous day:
select @s = dateadd(day, datediff(day, 1, getdate()), 0)
       ,@e = dateadd(day, datediff(day, 0, getdate()), 0)

--utc day range localized
select dateadd(hour, @utc_offset, @s)
       ,dateadd(hour, @utc_offset, @e)

OUTPUT:
2009-07-08 20:00:00.000 2009-07-09 20:00:00.000

--So your WHERE clause would be:
where [dateTime] >= dateadd(hour, @utc_offset, @s)
and    [dateTime] < dateadd(hour, @utc_offset, @e)

EDIT:
Mostly

by Lamprey

Be One with the Optimizer
TG

nonsec
Starting Member

26 Posts

Posted - 2009-07-10 : 14:52:03

Lamprey,

There is +4 hours difference between utc and system time. When I add records into database for example now time is :
2009-07-10 2:33:00.00 in the database it looks 2009-07-10 6:33:00.00 ok.

When I pull previous day data lets say:
2009-07-08 00:00:00.00
and
2009-07-08 23:59:59.00

When I run the query for previous day that is not going to be actual data because actual data is +4 hours ahead then the returned data right? or am I wrong?

Sorry but I am just confused this UTC offset things.

Your query works fine but am I getting accurate data for the previous day?

nonsec
Starting Member

26 Posts

Posted - 2009-07-10 : 14:56:35

TG, Lamprey,
Thank you I am going to try this one hope it helps : )

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 15:07:31

Now you got me curious...

>>When I run the query for previous day that is not going to be actual data because actual data is +4 hours ahead then the returned data right? or am I wrong?

Depends - You need to define your terms.
What does the [dateTime] values represent and from what timezone are they based?

If you wanted to record the start time of a phone conversation you were having with your Friend in Greenwich. Your wrist watch says it's 9:00 AM. Your Friend's watch says its 1:00 PM. What time is stored in your database? Is your query is saying show me all the calls that started at 1:00 PM Greenwich time? So you need to ask: "WHERE [datetime]+4hrs = 1:00 PM." Which would return your call at 9:00 AM?

Be One with the Optimizer
TG

nonsec
Starting Member

26 Posts

Posted - 2009-07-10 : 15:44:43

ok TG,
now it is getting more clear.

My time zone is GMT -5 Eastern (USA Canada) This is system time when I query the database for the records for example get me records for today after 3:00 PM; Now time is 3:42 PM in Pennsylvania

here is my Where clause ;

Where(Perf.vPerfRaw.DateTime > '2009-07-10 15:00:00.00' )

but results I am getting for 7:00 PM
2009-07-10 19:34:01.000
2009-07-10 19:34:01.000
2009-07-10 19:33:00.000
2009-07-10 19:33:00.000
2009-07-10 19:32:00.000
2009-07-10 19:32:00.000
2009-07-10 19:31:00.000

and when I run the these queries;

SELECT GETDATE()
2009-07-10 15:42:01.327

select GetUTCDate()
2009-07-10 19:42:01.327

you see the differences sql time is +4 hours ahead then the actual data record (system) time.

now I think I have to adjust my query something like this;

"WHERE [datetime]-4hrs = 1:00 PM

is that correct?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 16:14:33

those results satisfy your criteria in that they are: > '2009-07-10 15:00:00.00' maybe there are no rows between hour 15 and 19?

what happens if you query for:

Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'

Do you still get values in hour 19? if so:

Is your Sql Server physically in the same time-zone you are? Are you directly connecting via Enterprise manager or are you using an application via a web service or something else to issue your request? I'm wondering if some middle tier is adjusting your input datetimes for the timezone offset.

Be One with the Optimizer
TG

nonsec
Starting Member

26 Posts

Posted - 2009-07-10 : 16:43:52

TG,

This is one of Microsoft Product ( Operation Manager ) I believe by default application automatically insert data in that time zone. (+4 hours)

SQL Server and all servers and me, we are all in the same location and same time zone. I am using SQL server management studio.

"""""those results satisfy your criteria in that they are: > '2009-07-10 15:00:00.00' maybe there are no rows between hour 15 and 19?

A: yes there are rows between 15 to 19 because actual 15 is in the db is 19.

what happens if you query for:

Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'
"""""
A: I query db for the Where clause and of course it returns data because where we query this;
Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'

it is actually equal to this.

Where Perf.vPerfRaw.DateTime >= '2009-07-10 11:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 12:00:00.00'

I think Operation Manager uses it own timezone offset when inserts data.

so far no luck.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-10 : 17:05:28

This was a little ambiguous:

quote:
A: I query db for the Where clause and of course it returns data because where we query this;
Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'

it is actually equal to this.

Where Perf.vPerfRaw.DateTime >= '2009-07-10 11:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 12:00:00.00'

I guess you mean that you got what you asked for - rows with datetime values in the 15-16 hour. But those times returned represent UTC times not GMT times.

So you need to use the offset. My code (after Lamprey's post) should do it although I may have reversed the offset logic. Here is the corrected version:


declare @s datetime
       ,@e datetime
       ,@UTC_Offset int

set @utc_offset = datediff(hour, getdate(), getutcdate())

--daterange for previous day:
select @s = dateadd(day, datediff(day, 1, getdate()), 0)
       ,@e = dateadd(day, datediff(day, 0, getdate()), 0)

--utc day range localized
select dateadd(hour, @utc_offset, @s)
       ,dateadd(hour, @utc_offset, @e)

--This proves we have our offset working right.  These should return the same value
select getutcdate() [UTCdate]
       ,dateadd(hour, @utc_offset, getDate()) [getDateWithOffsetApplied]


OUTPUT:
2009-07-09 04:00:00.000 2009-07-10 04:00:00.000


UTCdate                 getDateWithOffsetApplied
----------------------- ------------------------
2009-07-10 21:04:03.783 2009-07-10 21:04:03.783


--So your WHERE clause would be:
where [dateTime] >= dateadd(hour, @utc_offset, @s)
and    [dateTime] < dateadd(hour, @utc_offset, @e)

Be One with the Optimizer
TG

nonsec
Starting Member

26 Posts

Posted - 2009-07-10 : 17:17:14

TG,

Thank you, I am going to try this and let you know results. I really appreciate your effort and time.

nonsec
Starting Member

26 Posts

Posted - 2009-07-17 : 08:49:50

TG, Lamprey,

Thank you very much, I really appreciate it. It worked : )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources