Daylight Savings Time Change

Can you tell me what kind of impact we are looking at from SQL server point
of view for the daylight savings time change that is happening soon.
SQL 7.0
SQL 2000
SQL 2005
Will there be a hot fix?
ThanksSQL Server takes its date and time from Windows. There is no hot fix
required for SQL Server, since the DST change is dealt with at the operating
system layer.
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||Take a look into this URL:-
http://www.microsoft.com/windows/timezone/dst2007.mspx
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:17E4EF33-1859-4897-89D0-658979BF7E8C@.microsoft.com...
> Can you tell me what kind of impact we are looking at from SQL server
> point
> of view for the daylight savings time change that is happening soon.
> SQL 7.0
> SQL 2000
> SQL 2005
> Will there be a hot fix?
> Thanks|||"Aaron Bertrand [SQL Server MVP]" wrote:
> SQL Server takes its date and time from Windows. There is no hot fix
> required for SQL Server, since the DST change is dealt with at the operating
> system layer.
Technically, not quite. SQL Server Notification Services 2.0 RTM and SP1,
and SQL Server Notification Services 2005 SP1 are affected. They store time
zone info in a table NSTimeZoneDstOffsets. A fix will be required.
Linchi

DateTime Ranges

Hi..
I am facing a problem trying to determine whether a point in tie falls within a specific date and time range.
Here is an example..
Is 7/20/2007 1:23:45PM in the range between (Thursday 8:00 PM) To (Sunday 7:59 AM)
ThanksI've not got Crystal on this PC, so excuse any errors, but I'd expect you could do something like

numbervar d := dayofweek({date}); //or whatever the 'get day' function is!
timevar t := ctime({date}); //Get just the time part

//return whether between Thursday 8pm and Sunday 8pm
(d = CrThursday and t >= ctime(20, 0, 0))
or d = CrFriday
or d = CrSaturday
or (d = CrSunday and t < ctime(20, 0, 0))|||Thanks my friend,

I used your CRsyntax and converted it to Basic as follows:

Dim d As number
Dim t AS time

d= dayofweek(currentdatetime)
t= ctime(currentdatetime)

'return whether between Thursday 8pm and Sunday 8pm
IF (d = CrThursday and t >= ctime(20, 0, 0)) or d = CrFriday or d = CrSaturday or (d = CrSunday and t < ctime(8, 0, 0)) THEN
FORMULA= "Code if True"
Else
FORMULA= "Code if False"
END IF

DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...

datetime

i'm using datediff to get the elapsed time b/t a timestamp and the current time. at this point i'm putting the answer in minutes, but i would like to format it to be similar to HH:MM.
how do i do this in sql server??
thanks in advance
e3wittselect cast(datediff(mi,'05/28/2004',getdate())/60 as varchar)+':'
+cast(datediff(mi,'05/28/2004',getdate())-(datediff(mi,'05/28/2004',getdate())/60)*60 as varchar)|||SET ANSI_NULLS OFF
SET NOCOUNT ON
GO

if object_id(N'dbo.fn_ElapsedTime') is not null begin
drop function dbo.fn_ElapsedTime
print 'Function dbo.fn_ElapsedTime dropped'
end
go

CREATE function fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null)
returns varchar(40)
as
begin
declare @.d int, @.h int, @.m int, @.s int, @.ms int, @.dif1 int, @.ret varchar(40)
select @.d = 0, @.h = 0, @.m = 0, @.s = 0, @.ms = 0

set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + ':' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end
go

if object_id(N'dbo.fn_ElapsedTime') is not null begin
print 'Function dbo.fn_ElapsedTime created'
end
go|||ok... now it's working just the way i was wanting.

thank you.|||Don't forget about the modulo operator (%). It's hand for converting time values:

select cast(datediff(mi, [TimeStamp], getdate())/60 as int) + ':' + (datediff(mi, [TimeStamp], getdate()) % 60)

DateTime

Hello,

Please can you point me in the right direction.

I have a table (JobMain) that holds a list of all jobs that out
engineers need to work on or have worked on.

I would like to select jobs that fall between Monday - Friday 8am to
7pm.

The tables holds a load of colums ... the two that are of interest to
me are

JobId (Primary Key) - Job Number
StartDate (Datetime datatype) - Job start date and time

regards

RobOn Jun 19, 4:21 pm, roblowein <rob.low...@.gmail.comwrote:

Quote:

Originally Posted by

Hello,
>
Please can you point me in the right direction.
>
I have a table (JobMain) that holds a list of all jobs that out
engineers need to work on or have worked on.
>
I would like to select jobs that fall between Monday - Friday 8am to
7pm.
>
The tables holds a load of colums ... the two that are of interest to
me are
>
JobId (Primary Key) - Job Number
StartDate (Datetime datatype) - Job start date and time
>
regards
>
Rob

DECLARE @.startdate datetime ,@.enddate datetime

SET @.startdate = '2007-06-18 08:00:00' -- Monday 8 AM
SET @.enddate = DATEADD(DD,4 ,DATEADD(HOUR,11,@.startdate)) --

SELECT @.startdate,@.enddate

SELECT * FROM yourtable
WHERE StartDate BETWEEN @.startdate AND enddate|||Hello, Rob

Try something like this:

SELECT * FROM YourTable
WHERE DATEPART(hh,StartDate) BETWEEN 8 AND 18
AND (DATEPART(w,StartDate)+@.@.DATEFIRST)%7 BETWEEN 2 AND 6

Razvan|||On 19 Jun, 12:56, Razvan Socol <rso...@.gmail.comwrote:

Quote:

Originally Posted by

Hello, Rob
>
Try something like this:
>
SELECT * FROM YourTable
WHERE DATEPART(hh,StartDate) BETWEEN 8 AND 18
AND (DATEPART(w,StartDate)+@.@.DATEFIRST)%7 BETWEEN 2 AND 6
>
Razvan

Thanks to you both...

A lot of help..

Regards

Rob