Finding jobs that wont run due to Daylight Savings Time?

Since this weekend is Daylight Savings, we want to make sure of any
jobs that won't run due to the "missing hour".

I have written a small query against sysjobschedules that tells me
which ones have a schedule that runs between 2 and 3. However, it
doesn't include any jobs that run every X minutes/hours, and so might
be run during that time. (i.e. a job that starts at midnight and runs
until 6, running every 2 hours)

Is there any (easy) way to determine this? I might be able to build a
function that uses the other fields in sysjobschedules to give a list
of times that the job will run, but I haven't gotten to that point yet.
Figured someone might have something already, rather than reinvent the
wheel.

Thanks,
MichaelI don't believe there's any easy way, but sysjobhistory might be a good
starting point - if a job ran between 2 and 3 in the past, it will
probably do so again. You could join on sysjobschedules to narrow it
down to daily schedules, schedules on a certain weekday or date etc.
And you can check the next_run_date column as well, of course.

Simon

Finding due date from a six character long date field

Hi All,
Here is my problem.
I have a datefield as varchar and six characters long.
The date is shown as 200102 which means the entire month of February
in 2001.
I need to find the due date based on this adding 30 days and the date
will be the last date of the month.
For ex.
if the bill date is 200102 the due date will be 03/30/2001
if the bill date is 200112 the due date will be 01/31/2002
and so on.
Could anyone please tell me how can I write the query?
I have given the DDLs below:
CREATE TABLE [dbo].[Table1] (
[ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
)
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200101')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200102')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200103')
Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
values('K123', 'BGZ12', '500001', '200112')
I was trying to run the following query:
SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
INVOICENO='500004'
of course, that did not give me the right answer.
I do highly appreciate your help.
Thanks a million in advance.
Best regards,
mamunHello Magmun !
Try this:
--German Version with dot seperated
Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
left(SUPLBILLDT,4)))) from Table1
--English Version with commy separated
Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
left(SUPLBILLDT,4)))) from Table1
HTH, Jens Süßmeyer.|||How about
SELECT due_date = DATEADD
(
MONTH,
2,
CONVERT(SMALLDATETIME, bill_date + '01', 112)
)-1
FROM table
(I assume you meant 03/31/2001, since 03/30 is not the last day of March.
And also, you're not adding 30 days to 20011201 and then setting the due
date to be the last day of that month, because 20011201 + 30 = 20011231 and
the last day of that month is 12/31, not 20020131. In other words, the
description of your requirements does not match your sample output... so if
the above query doesn't do what you want, you'll need to clarify;
preferably, with actual table structure, sample data (in the form of a
functional insert statement), and desired results.)
"Mamun" <mamun_ah@.hotmail.com> wrote in message
news:6012e7ab.0308220710.1ed91260@.posting.google.com...
> Hi All,
> Here is my problem.
> I have a datefield as varchar and six characters long.
> The date is shown as 200102 which means the entire month of February
> in 2001.
> I need to find the due date based on this adding 30 days and the date
> will be the last date of the month.
> For ex.
> if the bill date is 200102 the due date will be 03/30/2001
> if the bill date is 200112 the due date will be 01/31/2002
> and so on.
> Could anyone please tell me how can I write the query?
>
> I have given the DDLs below:
>
>
> CREATE TABLE [dbo].[Table1] (
> [ClaimNumber] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [VENDORCDE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [INVOICENO] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [SUPLBILLDT] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> )
>
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200101')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200102')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200103')
> Insert into Table1(ClaimNumber, VENDORCDE, INVOICENO, SUPLBILLDT)
> values('K123', 'BGZ12', '500001', '200112')
> I was trying to run the following query:
> SELECT *, dateadd(day, 30, SUPLBILLDT)AS DueDate FROMTable1 where
> INVOICENO='500004'
> of course, that did not give me the right answer.
> I do highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> mamun|||Why not return as a datetime, then you can wrap whatever presentation you
want around it using CONVERT and style. In your method, btw, you are
assuming that all of your string concatenations will not break datetime; I'm
not sure this is a healthy assumption, since you may not want to write code
that relies on a specific language / dateformat setting, and the machine's
regional settings.
"Jens Süßmeyer" <jsuessmeyer@.[REJECT_SPAM]web.de> wrote in message
news:eM$OtKMaDHA.2668@.TK2MSFTNGP09.phx.gbl...
> Hello Magmun !
> Try this:
>
> --German Version with dot seperated
> Select dateadd(d,-1,dateadd(m,2,('01.' + right(SUPLBILLDT,2) + '.' +
> left(SUPLBILLDT,4)))) from Table1
> --English Version with commy separated
> Select dateadd(d,-1,dateadd(m,2,(right(SUPLBILLDT,2) + '/01/' +
> left(SUPLBILLDT,4)))) from Table1
>
> HTH, Jens Süßmeyer.
>|||OK, convinced :)). Didn´t thought about this solution, even we use it
(Yours, just implemented by ME) to keep track of our guarantee expiry dates
X-)).
Jens Süßmeyer.sql