DECLARE @StartDate datetime, @EndDate datetime, @StartHour datetime, @EndHour datetime,

@liTimeOffset INT

SET @liTimeOffset = 120

SELECT DateAdd(n, @liTimeOffset, LocationDateTime)

FROM mytable

WHERE

LocationDateTime >= (CONVERT(VARCHAR(10), @StartDate, 120) + RIGHT(CONVERT(VARCHAR, @StartHour, 100),7))

AND

LocationDateTime < (CONVERT(VARCHAR(10), @EndDate, 120) +...

Hi Lynn,

the procs we have use the following variations:

@StartDate = '2009-11-01'

@StartDate = '2009-11-01 12:00:00'

@EndDate = '2009-12-01'

@EndDate = '2009-12-01 23:59:59'

2 months ago I had to use your method below:

WHERE

...

Hi Circue

Usually you have @StartDate AND @EndDate parameters.

Now I want to also add @StartHour AND @EndHour.

The default would still be @StartDate AND @EndDate.

But if @StartHour AND @EndHour is chosen on...

Hi Jeff, I dont use winzip, I use winrar. Tried to download winzip, guess what ?

version 14 (latest) is 37 MB. So i looked for winzip 9 (about 4MB), every...

Hi Lynn, instead of using:

WHERE

tbl.YourDateColumn >= @StartDate

AND tbl.YourDateColumn < @EndDate

I prefer to use:

WHERE

tbl.YourDateColumn between @StartDate AND @EndDate.

-------------------------

Have a...

Hi Lynn, regarding the date issue which you and Jeff commented on. Well most of the stored procedures at my employer were written/coded from months to several years ago by...

Cant open a .rar (winrar) file ? Ah come on Jeff, never heard of anyone working in IT for years that does not have winrar installed. Go to...

Hi Lynn, I tested your code and it works 100%, on the example table I posted on 692 rows its just as quick as the cursor I added to my...

Hi SQLGuru, I tried your code but unfortunately it does not work. I get 670 rows instead of 692, the columns are also out of sync. If you want to...

Hi SQLGuru, go to the following URL:

http://www.sqlservercentral.com/Forums/Topic831649-338-1.aspx#bm831800

Hi Lynn, thanks for your speedy reply and solution. Much appreciated. Will test and also compare runtime of cursor vs your script, add the comparison results to this forum.

Hi Lynn, you're spot on, for the same ItemId there should be 1, 0, 1, 0.

View below:

INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)

VALUES ('412061','2009-11-05 03:54:00','','1')

INSERT INTO [tmpTable1] (ItemId,LocationDateTime,EndLocationDateTime,EventType)

...

From Lynn:

Is the following order of records guarantteed, or could you get a type 1 with no following type 0, or could you get 2 consecutive type 1's or 0's?

Hi...

Hi Jeff, posted as suggested.

Post #831649.

Topic: Replace cursor with while loop / compare cursor with while loop / optimize...

Hi Lynn, I attached the script to create the table and insert the data.

Please view attachment: script for tmpTable1.rar