Listing jobs that ran last hour

Hello...
I want to write a query that will list for me the name, date and time of
jobs that ran from the previous hour. I am using SQL Server 2000.
Thank you,
Brett>> ...a query that will list for me the name, date and time of jobs that ran
The data you are looking for is available in msdb..sysjobhistory table. You
can do:
SELECT job_name, run_time
FROM ( SELECT CAST( CAST( run_date AS CHAR( 9 ) ) +
STUFF( STUFF( RIGHT( REPLICATE( '0', 6 ) +
CAST( run_time AS VARCHAR ), 6 ) , 3, 0 , ':' ) , 6, 0 ,
':' )
AS DATETIME ),
( SELECT name
FROM msdb..sysjobs s2
WHERE s2.job_id = s1.job_id )
FROM msdb..sysjobhistory s1 ) D ( run_time, job_name )
WHERE run_time >= DATEADD( hour, -1, CURRENT_TIMESTAMP ) ;
Perhaps, someone else might post a more simplified string format routine.
Anith

Listening for report events

I have a series of reports and want to fire off a stored procedure to update
a "Printed date" column in my database. I don't want to do this from the
dataset that populates my datset as I only want this to occur if the report
is successfully generated. Is there any way I can do this with the scheduler?
If not, are the any report events that I can listen for? And or any other
solution to this problem.Sorry. I don't think I made my intentions very clear. I meant to say that I
have a series of report, and want to fire off a stored procedure everytime a
report is successfully generated.
"Graham Hope" wrote:
> I have a series of reports and want to fire off a stored procedure to update
> a "Printed date" column in my database. I don't want to do this from the
> dataset that populates my datset as I only want this to occur if the report
> is successfully generated. Is there any way I can do this with the scheduler?
> If not, are the any report events that I can listen for? And or any other
> solution to this problem.

ListAvailableSQLServers (SQLDMO) not up to date

Hi NG,
MSSQL2k+SP3a+MDAC2.8
If I use ListAvailableSQLServers (SQLDMO)
I get after 2 or 3 seconds a Serverlist.
But the serverlist is not up to date.
Thats means a server which shut down 20 seconds before
apperas in the serverlist. A server which started 20 seconds before
dont show in the list.
Thanks ThomasHi
As far as I can tell, the list is cached for a short time to help with
network performance. You will have to experiment to find the exact
timeframe.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:42302c73.366146156@.news.t-online.de...
> Hi NG,
> MSSQL2k+SP3a+MDAC2.8
> If I use ListAvailableSQLServers (SQLDMO)
> I get after 2 or 3 seconds a Serverlist.
> But the serverlist is not up to date.
> Thats means a server which shut down 20 seconds before
> apperas in the serverlist. A server which started 20 seconds before
> dont show in the list.
> Thanks Thomas

List up-coming birthdays

I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.

Regards,
Jacques Matthee.SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||oups !

that needs a little reajustment
on moment please...|||Thanks for the quick reply! i'll give it a bash!

Originally posted by Karolyn
SELECT *
FROM Employes
WHERE DATEDIFF(day, birthday, getdate())<=30|||now it returns everything, because the birthdays are in the 1970s & 80s compared to the current year which is 2004.

Originally posted by jacmat
Thanks for the quick reply! i'll give it a bash!|||it doesn't work
I'm creating a function for you
that you'll able to use the get the good results|||just a few minutes ...|||select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30|||create this function

CREATE function dbo.DatePart(@.Date Varchar(26), @.Format VarChar(20))
returns Varchar(10) as
begin

return( (case @.Format
when 'YYYY' then Convert(char(4), Year(@.Date))
when 'MM' then Replicate('0', Len(Cast(Month(@.Date) as char(2)))) + Cast(Month(@.Date) as char(2))
when 'DD' then Replicate('0', Len(Cast(Day(@.Date) as char(2)))) + Cast(Day(@.Date) as char(2))
end))
end|||SELECT * FROM Employes
Where
DateDiff(day,
Cast(
RTrim(dbo.DatePart(getdate(), 'YYYY')) + '-' +
RTrim(dbo.DatePart('2002-01-02','DD')) + '-' +
RTrim(dbo.DatePart('2002-01-02','MM')) as datetime),getdate())<=30|||r937 solution's is quite is simpler...|||I always forget the convert format number (like 101)

very practical...
I'll remember this|||Karolyn, you have to put getdate() as the 2nd parameter in DATEDIFF because you want birthdays 30 days after today, not 30 days before|||a soooooo little detail

or put -30|||hey, R937 can you answer my concat null option question ?
(posted recently)|||Originally posted by jacmat
I've got an employee table with a date of birth field in it. i need a query that will a allow me to list all employees who's birthdays are coming up the next 30 days (or 1 month, if easier). I've tried several approaches & am getting nowhere... Any help would be greatly appreciated.

Regards,
Jacques Matthee.
DOB = The field Name
BDAYS = The table name

SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30|||Originally posted by r937
select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(convert(char(4),year(getdate()) & '/'
& convert(char(5),dob,101)
as datetime)
) <= 30

This may not work at the end of the year. It will skip the people born on January.|||your right

he should test on the month and
add an OR for a test on the next year|||Originally posted by Karolyn
your right

he should test on the month and
add an OR for a test on the next year

I guess this should work. BETWEEN is the best way to go

DOB = The field Name
BDAYS = The table name

====================================
SELECT DOB, CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
FROM BDAYS
WHERE CAST(MONTH(DOB) AS VARCHAR(2)) + '-' + CAST(DAY(DOB)AS VARCHAR(2)) + '-' + CAST(YEAR(GETDATE()) AS VARCHAR(4))
BETWEEN
GETDATE() AND GETDATE() + 30
===============================|||of course my query handles the year-end january boundary!!

however, it did have a few typos in it :rolleyes:

and the test has to be between 0 and 30, not just less than or equal to 30

so in the meantime, i have tested it

this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||Even though you have added BETWEEN in the WHERE clause it still would not work!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Not only for January it would not work if the day falls 30 behind the dates

The DateDiff function would return minus values also.

By the way Toronto is nice city. I used live for a year and half. I love that city

Originally posted by r937
of course my query handles the year-end january boundary!!

however, it did have a few typos in it :rolleyes:

and the test has to be between 0 and 30, not just less than or equal to 30

so in the meantime, i have tested it

this works --select id
, name
, dob
from employees
where datediff(dd
, getdate()
, cast(
cast(year(getdate()) as char(4))
+ '/' + convert(char(5),dob,101)
as datetime)
) between 0 and 30|||you're absolutely right, and i am wrong

i withdraw my solutions

not enough coffee, i guess

i do have a solution, which also prints out the actual age of the person as well, but it was done in access, so i would need a few moments to convert it to sql server syntax

see http://www.dbforums.com/showthread.php?threadid=659590

let me know if you want the conversion|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30

Itz ya birf-day!|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30
that is brilliant

it took me about an hour to figure it out and play with it against my test table of birthdays, starting from the innermost function and working out, to see how it works

that formula is fabulous

did you write that?|||Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.

It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.

I'm not sure how it works around leap years. The dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate) function is supposed to account for it, but it may still be 1 day off.|||Originally posted by blindman
Yeah. I took the challenge because the problem looked so deceptively simple. I started with the idea of some ad-hoc implementation of modulo arithmetic, and then simplified it down to the datepart method while I was driving in my car to get some lunch.

It's amazing how many complex programming problems can be solved behind a steering wheel that couldn't be solved in front of a monitor.or in the bathtub

it works fine around leap years:
...dob... ..today... bdaythisyr adj bdayadjust day
1977-12-09 2004-01-31 2004-12-09 -30 2004-11-09 313
1977-12-10 2004-01-31 2004-12-10 -30 2004-11-10 314
1977-12-11 2004-01-31 2004-12-11 -30 2004-11-11 315
1977-12-29 2004-01-31 2004-12-29 -30 2004-11-29 333
1977-12-30 2004-01-31 2004-12-30 -30 2004-11-30 334
1977-12-31 2004-01-31 2004-12-31 -30 2004-12-01 335
1978-01-01 2004-01-31 2004-01-01 -30 2003-12-02 335
1978-01-02 2004-01-31 2004-01-02 -30 2003-12-03 336
1978-01-03 2004-01-31 2004-01-03 -30 2003-12-04 337
1978-01-04 2004-01-31 2004-01-04 -30 2003-12-05 338
1978-01-05 2004-01-31 2004-01-05 -30 2003-12-06 339
1979-01-10 2004-01-31 2004-01-10 -30 2003-12-11 344
1980-02-04 2004-01-31 2004-02-04 -30 2004-01-05 4
1980-02-05 2004-01-31 2004-02-05 -30 2004-01-06 5
1980-02-06 2004-01-31 2004-02-06 -30 2004-01-07 6
1980-02-28 2004-01-31 2004-02-28 -30 2004-01-29 28 <--
1980-02-29 2004-01-31 2004-02-29 -30 2004-01-30 29 <--
1980-03-01 2004-01-31 2004-03-01 -30 2004-01-31 30 <--
1984-06-14 2004-01-31 2004-06-14 -30 2004-05-15 135|||If you like it, put it on your web page.

That way I'll be able to find it if I ever need it!

Cheers!|||thanks, i will

:cool:|||It will be my greatest honor to have my code engraved in the Book Of Limeback!|||attribution to: blindman

or any other name if you wish (contact me via email)|||Originally posted by blindman
Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30

Itz ya birf-day!

Blindman could you help me out plz?

I have applied your coding as:

Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30

I get an error message saying,

Syntax error (missing operator) in query expression'Where datepart(dy, dateadd(d, -datepart(dy,Date())+1, dateadd(year, datediff(year, @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30'.

What shall I do to correct this? I am writing this in Access and have been told to change GetDate to Date.|||Is this an Access database or an Access Data Project connected to a SQL Server database?

Access syntax is slightly different than SQL for many functions, including the datepart function.

Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30

...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.|||Using Access, I prefer to get the next anniversary of a given date using:DateAdd("yyyy",DateDiff("yyyy",[dob],Now())+IIf(Format(Now(),"mmdd")<Format([dob],"mmdd"),0,1),[dob])Once you've got that, you can do simple date compares to get the rows that interest you.

-PatP|||Originally posted by blindman
Is this an Access database or an Access Data Project connected to a SQL Server database?

Access syntax is slightly different than SQL for many functions, including the datepart function.

Where datepart("y", dateadd("d", -datepart("y",Date())+1, dateadd("yyyy", datediff("yyyy", @.MemberExpiryDate, Date()), @.MemberExpiryDate)))-1 between 0 and 30

...but I'm concerned about how you are passing the @.MemberExpiryDate parameter and how you are planning to execute the sql code.

This is an Access database.

I have used the following code posted below and it seems to be working.

SELECT member_name, member_id
from your_table
where join_date <= now() + 20

What do you make of it?|||Originally posted by hali99
This is an Access database.

I have used the following code posted below and it seems to be working.

SELECT member_name, member_id
from your_table
where join_date <= now() + 20

What do you make of it? I read that as "show me the members that plan to join sometime in the next 20 days" which isn't quite what I think you want! At least if your join_date column shows the date that the member originally joined.

If I read that correctly, I'd use something like:SELECT member_name, member_id
from your_table
where DateAdd("yyyy",DateDiff("yyyy",[join_date],Now())
+ IIf(Format(Now(),"mmdd")<Format([join_date],"mmdd"),0,1),[join_date]) BETWEEN now() -2 AND now() + 20This will show you the members who expired in the last two day and the members that will expire in the next 20 days.

-PatP|||Where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, @.BirthDate, getDate()), @.BirthDate)))-1 between 0 and 30blindman, somebody emailed me that this isn't working correctly (if you recall, it's on my web site)

i have tested it and have confirmed that it's broken

create table birthdays
( id tinyint not null primary key identity
, birthday datetime
)
insert into birthdays (birthday) values ('1926-12-26')
insert into birthdays (birthday) values ('1927-12-27')
insert into birthdays (birthday) values ('1928-12-28')
insert into birthdays (birthday) values ('1929-12-29')
insert into birthdays (birthday) values ('1930-12-30')
insert into birthdays (birthday) values ('1931-12-31')
insert into birthdays (birthday) values ('1951-01-01')
insert into birthdays (birthday) values ('1952-01-02')
insert into birthdays (birthday) values ('1953-01-03')
insert into birthdays (birthday) values ('1954-01-04')
insert into birthdays (birthday) values ('1955-01-05')
insert into birthdays (birthday) values ('1956-01-06')
insert into birthdays (birthday) values ('1957-01-07')

select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
=0
1929-12-29 00:00:00.000

select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 1
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000

select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 2
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000

select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 3
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000

select * from birthdays
where datepart(dy, dateadd(d, -datepart(dy, GetDate())+1, dateadd(year, datediff(year, birthday, getDate()), birthday)))-1
between 0 and 4
1929-12-29 00:00:00.000
1930-12-30 00:00:00.000
1931-12-31 00:00:00.000
1951-01-01 00:00:00.000

it seems to mess up on the year boundary|||I'll check it out. Thanks.|||This should work, but slightly differently:set nocount on

create table AnniversaryDates
( id tinyint not null primary key identity
, AnniversaryDate datetime
)
insert into AnniversaryDates (AnniversaryDate) values ('1926-12-26')
insert into AnniversaryDates (AnniversaryDate) values ('1927-12-27')
insert into AnniversaryDates (AnniversaryDate) values ('1928-12-28')
insert into AnniversaryDates (AnniversaryDate) values ('1929-12-29')
insert into AnniversaryDates (AnniversaryDate) values ('1930-12-30')
insert into AnniversaryDates (AnniversaryDate) values ('1931-12-31')
insert into AnniversaryDates (AnniversaryDate) values ('1951-01-01')
insert into AnniversaryDates (AnniversaryDate) values ('1952-01-02')
insert into AnniversaryDates (AnniversaryDate) values ('1953-01-03')
insert into AnniversaryDates (AnniversaryDate) values ('1954-01-04')
insert into AnniversaryDates (AnniversaryDate) values ('1955-01-05')
insert into AnniversaryDates (AnniversaryDate) values ('1956-01-06')
insert into AnniversaryDates (AnniversaryDate) values ('1957-01-07')

declare @.CurrentDate datetime
set @.CurrentDate = '2005-12-28'

select AnniversaryDate,
dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate) as NextAnniversaryDate,
datediff(d, @.CurrentDate, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate) + cast(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate) + abs(datediff(d, dateadd(year, datediff(year, AnniversaryDate, @.CurrentDate), AnniversaryDate), @.CurrentDate)) as bit), AnniversaryDate)) as DaysToAnniversaryDate
from AnniversaryDates

drop table AnniversaryDates
Using @.CurrentDate allows you to test four consecutive years to verify no problems with leap years, and also allows you to create the formula as a UDF.

List of past Dates

Hello,
I want to create a list op dates using the SELECT statement. The dates range
from the current date till the current date - 10 days
The resultset for today should be as follows.
20050221
20050220
20050219
20050218
20050217
20050216
20050215
20050214
20050213
20050212
20050211
How do I create this. The result must be used in an other view, so if
possible I want a view.
Thanks
BartDoes this work for you?
select getdate()
union all
select getdate()-1
union all
select getdate()-2
union all
select getdate()-3
union all
select getdate()-4
union all
select getdate()-5
union all
select getdate()-6
union all
select getdate()-7
union all
select getdate()-8
union all
select getdate()-9
Bojidar Alexandro
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||Hi,
Maybe this will help you.
Tomasz B.
use tempdb
go
create view ten_dates
As
select convert(varchar(8), getdate(), 112) d
union all
select convert(varchar(8), getdate()-1, 112)
union all
select convert(varchar(8), getdate()-2, 112)
union all
select convert(varchar(8), getdate()-3, 112)
union all
select convert(varchar(8), getdate()-4, 112)
union all
select convert(varchar(8), getdate()-5, 112)
union all
select convert(varchar(8), getdate()-6, 112)
union all
select convert(varchar(8), getdate()-7, 112)
union all
select convert(varchar(8), getdate()-8, 112)
union all
select convert(varchar(8), getdate()-9, 112)
union all
select convert(varchar(8), getdate()-10, 112)
"Bart Steur" wrote:

> Hello,
> I want to create a list op dates using the SELECT statement. The dates ran
ge
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>
>|||Bart
Look at the script written by Irzik Ben-Gan
CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @.rc AS INT
SET @.rc = 1
INSERT INTO @.Dates VALUES(@.from)
WHILE @.from + @.rc * 2 - 1 <= @.to
BEGIN
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
SET @.rc = @.rc * 2
END
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
WHERE dt + @.rc <= @.to
RETURN
END
GO
SELECT dt FROM fn_dates('20050211', '20050221')
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||SELECT dt
FROM Calendar
WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
http://www.aspfaq.com/show.asp?id=2519
David Portas
SQL Server MVP
--|||Thanks Guys,
It worked.
Bart
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3CD7AEB-9F22-435F-A569-32CE2D005F45@.microsoft.com...
> SELECT dt
> FROM Calendar
> WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
> http://www.aspfaq.com/show.asp?id=2519
> --
> David Portas
> SQL Server MVP
> --
>

list of months between two date

hi all

i need a query which will give the list of all the months between two given dates

I am not sure this works in all query languages, but in T-SQL you can use the following:

SELECT DATEDIFF(m, GetDate(), '12/25/2006')

This gives you the number of months between today and Christmas. Running that query in SQL will return "1" (if you ran it today anyway). The structure for the command is:

DATEDIFF(datepart, startdate, enddate)

For the datepart, you use "m" to indicate you want the number of months. You could also use "d" for days or "y" for years.

Hope this helps.

-Jacob

|||

no jacob i need list of months between two dates

forexample

input:

startdate:1/1/2006

enddate:1/12/2006

output:

jan-2006

feb-2006

.....

......

dec-2006

|||

Funny, rather work on other bugs right now than my own heehee, if anyone knows how to print a barcode from MS Reporting Services let me know.
I have a thread about ithere.

But here's some code for vmssanthosh:

DECLARE @.date1datetime, @.date2datetime, @.totalMonthsint, @.counterintSET @.date1 ='01/01/2006'SET @.date2 ='01/01/2007'--'02/02/2007'SET @.totalMonths =DATEDIFF(m, @.date1, @.date2)if(@.totalMonths < 0)SELECT'Second date parameter is prior to the first date parameter'if(@.totalMonths =0)SELECT'Same month'elseBEGINcreate table #temp_months(nameOfMonthvarchar(9))SET @.counter = 0WHILE @.counter < @.totalMonthsBEGININSERT INTO #temp_months (nameOfMonth)VALUES (DATENAME(month, @.date1))SET @.counter = @.counter + 1SET @.date1 =DATEADD(Month, 1, @.date1)ENDSELECT *FROM #temp_monthsEND

|||

For the format you specified, change the INSERT statement to this:

INSERT INTO #temp_months (nameOfMonth)VALUES (LEFT(DATENAME(month, @.date1),3) +'-' +DATENAME(year, @.date1))

List Filtering NOT Working

I have a Report where i want to show in a DataList only those values Filtered by one of the fields of my dataset. I want to show a Date value ONLY if another column value in that row in my dataset = "IN".

But nothing seems to happen here. No records are shown. It's like the condition is never true.

Steps:

1. I drag a DataList

2. I drag a DatasetField inside the DataList (automatically creates a textbox)

3. Right Click on the Datalist, Properties, then Filter Tab

I use the following statement on the Filter tab

=Cstr(Fields!TransactionType.Value) Like "IN"

or

=Cstr(Fields!TransactionType.Value) = "IN"

it doesnt work at all.

Does anyone knows what's going on?

Thanks

Jose

I've noticed that in filtering sometimes, in fact in most cases, I have to explicitly cast each side to the correct data type. So in your case

Expression: Operator: Value:

=Cstr(Fields!TransactionType.Value) = = Cstr("IN")

Hope this helps.

|||

PERFECTLY FINE

THANK YOU

List Dates Between Min & Max Dates

I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min & Max
Dates
Example.
Min Date: 1/1/2005
Max Date: 5/1/2005
Dates between would be 2/1/2005,3/1/2005,4/1/2005
Please advise and thanks for your help.
GalahadThat's an interesting call. Here is my solution.
The idea is to get a list of sequential numbers: 1, 2, 3, ... To generate r
ows, I choose sys.all_columns view because it has probably the most number o
f rows (5000+ in a small database). This could be a weak point, but should s
atisfy normal business requirements.
select top(datediff(d,'2/1/2006','2/10/2006')+1)
cast('2/1/2006' as datetime)+
(select count(*) from sys.all_columns b where b.object_id*cast(1000 as bigin
t)+b.column_id<=a.object_id*cast(1000 as bigint)+a.column_id) - 1 val
from sys.all_columns a order by object_id, column_id
If you want only the first day of the month, it shouldn't be hard to accompl
ish with the same idea.
To get a comma delimited list, use the list() function I discussed about in
another thread:
select dbo.list(convert(char(10),val,101)) from ([The SQL Above]) tbl
"Galahad" <Galahad@.discussions.microsoft.com> wrote in message news:4339C758-529D-415C-BD23
-9C0F70EE1366@.microsoft.com...
> I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min & Ma
x
> Dates
>
> Example.
>
> Min Date: 1/1/2005
> Max Date: 5/1/2005
>
> Dates between would be 2/1/2005,3/1/2005,4/1/2005
>
> Please advise and thanks for your help.
>
> Galahad|||http://www.aspfaq.com/show.asp?id=2519
"Galahad" <Galahad@.discussions.microsoft.com> wrote in message
news:4339C758-529D-415C-BD23-9C0F70EE1366@.microsoft.com...
> I'm trying to find a way to list the dates (XXXX-XX-XX) between a Min &
> Max
> Dates
> Example.
> Min Date: 1/1/2005
> Max Date: 5/1/2005
> Dates between would be 2/1/2005,3/1/2005,4/1/2005
> Please advise and thanks for your help.
> Galahad|||The callendar table is a brilliant solution:
http://www.aspfaq.com/show.asp?id=2519
Of course datatime is not the only data type that can be used.
Don't forget to thank the guys at aspfaq (http://www.aspfaq.com/credits.asp)
.
ML
http://milambda.blogspot.com/