I have a stored procedure using Convert where the exact same Convert
string works in the SELECT portion of the procedure but fails in the
WHERE portion.
The entire SP is listed below.
Specifically, I have a problem with this portion in the WHERE clause:

DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between
CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @DateNow)),101) AND
tblMyEventTableName.RecurrenceEnd)

(tblMyEventTableName.ReminderDays = days ahead the user wants to be
reminded)

If I use DATEPART(Month, @DateNow)+1
it fails even though I use this in the SELECT portion. The error
mesage is "The conversion of char data type to smalldatetime data type
resulted in an out-of range smalldatetime value."
If I use DATEPART(Month, @DateNow)
it doesn't fail.
If I use DATEPART(Month, @DateNow)+2
it doesn't fail.
If I use DATEPART(Month, @DateNow)+3
it fails with error message "Error converting data type datetime to
smalldatetime."
If I use DATEPART(Month, @DateNow)+4
it doesn't fail.

What the SP is trying to do is to evaluate a series of dates in a
table of personal reminders. If the user has set the reminder to
"monthly", the SP evaluates if the day of the month has already passed
today's date, it so, it creates a reminder date (myReminderDate) with
next month's day of the month.
(RecurrenceEnd is the date the reminder is set to stop):


Alter Procedure SPExample
@DateNow smalldatetime
As
SELECT
CASE WHEN tblMyEventTableName.RecurrencePattern ='monthly' AND
DATEPART(d, @DateNow) <= DATEPART(d,tblMyEventTableName.TaskDateTime)
and tblMyEventTableName.RecurrenceEnd > @DateNow

/*monthly event: today day is less than or equal to than monthly task
month-day so remind this month*/

THEN CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @DateNow)),101) ELSE
CASE WHEN tblMyEventTableName.RecurrencePattern ='monthly' AND
DATEPART(d, @DateNow) > DATEPART(d,tblMyEventTableName.TaskDateTime)
and tblMyEventTableName.RecurrenceEnd > @DateNow

/*monthly event: today day is greater than monthly task month-day so
remind next month*/

THEN CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/'
+ str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @DateNow)),101) ELSE

/*RecurrencePattern is not set to monthly so just use the reminder
date*/
tblMyEventTableName.TaskDateTime
END
END
AS myReminderDate, tblMyEventTableName.myTaskName
FROM
tblMyEventTableName
WHERE

/* takes care of monthly events that are after or equal today's day of
year */

tblMyEventTableName.RecurrencePattern ='monthly'
AND
DATEPART(d, @DateNow) <= DATEPART(d,tblMyEventTableName.TaskDateTime)
AND
tblMyEventTableName.RecurrenceEnd > @DateNow
AND
DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between
CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @DateNow)),101) AND
tblMyEventTableName.RecurrenceEnd)

OR
/* takes care of monthly events that are before today's day of year */

tblMyEventTableName.RecurrencePattern ='monthly'
AND
DATEPART(d, @DateNow) > DATEPART(d,tblMyEventTableName.TaskDateTime)
AND
tblMyEventTableName.RecurrenceEnd > @DateNow
AND
DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between
CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @DateNow)),101) AND
tblMyEventTableName.RecurrenceEnd)

Lauren Quantrell (laurenquantrell@hotmail.com) writes:
> I have a stored procedure using Convert where the exact same Convert
> string works in the SELECT portion of the procedure but fails in the
> WHERE portion.
> The entire SP is listed below.
> Specifically, I have a problem with this portion in the WHERE clause:
>
> DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between
> CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' +
> str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> str(DATEPART(Year, @DateNow)),101) AND
> tblMyEventTableName.RecurrenceEnd)

Your entire procedure is a bit too complicated to encourage me to
dive into the details, at least not without the table definition
and sample data.

But if I understand the above correctly, it will not work if
taskdatetime is for instance 20030131, as you will land on the
non-existing date 20030231. It seems that you need to refine your
business rules to cover this case.

Also, I don't know how big your table is, but if there is a index
on blMyEventTableName.ReminderDays, the expression above will not
use that index, since the column is part of an expression.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns947A2B67E4B1Yazorman@127.0.0.1>...
> Lauren Quantrell (laurenquantrell@hotmail.com) writes:
> > I have a stored procedure using Convert where the exact same Convert
> > string works in the SELECT portion of the procedure but fails in the
> > WHERE portion.
> > The entire SP is listed below.
> > Specifically, I have a problem with this portion in the WHERE clause:
> >
> > DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between
> > CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' +
> > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> > str(DATEPART(Year, @DateNow)),101) AND
> > tblMyEventTableName.RecurrenceEnd)
>
> Your entire procedure is a bit too complicated to encourage me to
> dive into the details, at least not without the table definition
> and sample data.
>
> But if I understand the above correctly, it will not work if
> taskdatetime is for instance 20030131, as you will land on the
> non-existing date 20030231. It seems that you need to refine your
> business rules to cover this case.
>
> Also, I don't know how big your table is, but if there is a index
> on blMyEventTableName.ReminderDays, the expression above will not
> use that index, since the column is part of an expression.


I think the following clause is not correct:

CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1)

Basically you are attempting to convert an integer number, ie the
month + 1 to a smalldatetime. I would have expected something like:

CONVERT(int,str(DATEPART(Month, @DateNow)+1)


-- For example
declare @DateNow smalldatetime
select @DateNow = '31-Dec-2003'

-- This generates the error Syntax error converting character string
to smalldatetime data type.
select CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1))
-- This works
select CONVERT(int,str(DATEPART(Month, @DateNow)+1))

Mystery Man (PromisedOyster@hotmail.com) writes:
> I think the following clause is not correct:
>
> CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1)

Yes, taking out of context it is grossly incorrect, because there is a
right parathesis missing. This is the complete expression, reformatted
for legibility:

CONVERT(smalldatetime,
str(DATEPART(Month, @DateNow) + 1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @DateNow)),
101)

I did the same reflection as you, but I tried in QA to see what it
would actually return. It took a while to grasp that syntax error...

Anway, not even the complete expression is good, because it does not
work for dates like 2004-01-30.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

But the convert is being run on the whole section m/d/y:
CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' +
str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
str(DATEPART(Year, @DateNow)),101)

PromisedOyster@hotmail.com (Mystery Man) wrote in message news:<87c81238.0401240245.253f0a69@posting.google. com>...
> Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns947A2B67E4B1Yazorman@127.0.0.1>...
> > Lauren Quantrell (laurenquantrell@hotmail.com) writes:
> > > I have a stored procedure using Convert where the exact same Convert
> > > string works in the SELECT portion of the procedure but fails in the
> > > WHERE portion.
> > > The entire SP is listed below.
> > > Specifically, I have a problem with this portion in the WHERE clause:
> > >
> > > DATEADD(Day,tblMyEventTableName.ReminderDays, @DateNow) Between
> > > CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1) + '/' +
> > > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> > > str(DATEPART(Year, @DateNow)),101) AND
> > > tblMyEventTableName.RecurrenceEnd)
> >
> > Your entire procedure is a bit too complicated to encourage me to
> > dive into the details, at least not without the table definition
> > and sample data.
> >
> > But if I understand the above correctly, it will not work if
> > taskdatetime is for instance 20030131, as you will land on the
> > non-existing date 20030231. It seems that you need to refine your
> > business rules to cover this case.
> >
> > Also, I don't know how big your table is, but if there is a index
> > on blMyEventTableName.ReminderDays, the expression above will not
> > use that index, since the column is part of an expression.
>
>
> I think the following clause is not correct:
>
> CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1)
>
> Basically you are attempting to convert an integer number, ie the
> month + 1 to a smalldatetime. I would have expected something like:
>
> CONVERT(int,str(DATEPART(Month, @DateNow)+1)
>
>
> -- For example
> declare @DateNow smalldatetime
> select @DateNow = '31-Dec-2003'
>
> -- This generates the error Syntax error converting character string
> to smalldatetime data type.
> select CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1))
> -- This works
> select CONVERT(int,str(DATEPART(Month, @DateNow)+1))

Thanks!> I see my error. But how can I accomplish this?
I have a table of tasks with a smalldatetime taskdate field, an
integer field RemDays which is the days in advance they want to be
reminded, and a reminder interval field

A user enters a task/reminder set to Jan.30,2004.

They want to be reminded of this every month, four days in advance of
the date (the 30th of every month).
They log on, let's say the current date is Feb. 27, 2004.
So, since it's three days before Feb.30,2004 the event will show up on
their reminder list. (OK, so that's the first problem- there is NO
Feb.30!)

I want to create sql server SP code that determines that DATETODAY
(Feb. 27) is BETWEEN the (30th of the month) and (30th of the month -
4 (the days in advance to remind)).

That's what I was trying to accomplish in that convert statement,
though poorly!
In the same table are tasks that are not recurring, as well as tasks
with annual reminders (those were easy to figure out.)
I'm going to have the same problem with tasks with weekly reminders as
well.


The SP populates a form that shows all tasks on the reminder list in
descending order, so the task to be reminded on the 30th of every
month, shows in in line with the annual birthday reminder of someone
born on Feb. 28, 1957, and the one time (non recurring) task reminder
for an appointment on Feb. 27, 2004.

Any help is greatly appreciated.
lq


Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns947A9EA01D044Yazorman@127.0.0.1>...
> Mystery Man (PromisedOyster@hotmail.com) writes:
> > I think the following clause is not correct:
> >
> > CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1)
>
> Yes, taking out of context it is grossly incorrect, because there is a
> right parathesis missing. This is the complete expression, reformatted
> for legibility:
>
> CONVERT(smalldatetime,
> str(DATEPART(Month, @DateNow) + 1) + '/' +
> str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> str(DATEPART(Year, @DateNow)),
> 101)
>
> I did the same reflection as you, but I tried in QA to see what it
> would actually return. It took a while to grasp that syntax error...
>
> Anway, not even the complete expression is good, because it does not
> work for dates like 2004-01-30.

To further elaborate:

tblMyTaskTable:

Date Task ReminderInterval
ReminderDaysinAdvance
2/28/1957 Joe Smith Birthday Annual 7
1/30/2004 Rent Due Monthly 4
2/27/2004 Dentist Appointment None 3
1/25/2004 Shrink Appointment Weekly 2
1/25/2004 Car Payment Due Monthly 4

What I'm trying to produce is a form that shows this list when a user
logs in on Feb. 26:

Feb. 25 Shrink Appointment
Feb. 27 Dentist Appointment
Feb. 28 Joe Smith Birthday (1957)
Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
that too!)

(the car payment won't show up because today is Feb. 26 and the next
reminder won't trigger until four days before the 25th of the next
month.)

This is proving to be very complex!

Any help is appreciated.
lq








Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns947A9EA01D044Yazorman@127.0.0.1>...
> Mystery Man (PromisedOyster@hotmail.com) writes:
> > I think the following clause is not correct:
> >
> > CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1)
>
> Yes, taking out of context it is grossly incorrect, because there is a
> right parathesis missing. This is the complete expression, reformatted
> for legibility:
>
> CONVERT(smalldatetime,
> str(DATEPART(Month, @DateNow) + 1) + '/' +
> str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/' +
> str(DATEPART(Year, @DateNow)),
> 101)
>
> I did the same reflection as you, but I tried in QA to see what it
> would actually return. It took a while to grasp that syntax error...
>
> Anway, not even the complete expression is good, because it does not
> work for dates like 2004-01-30.

Lauren Quantrell (laurenquantrell@hotmail.com) writes:
> Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> that too!)
>
> (the car payment won't show up because today is Feb. 26 and the next
> reminder won't trigger until four days before the 25th of the next
> month.)
>
> This is proving to be very complex!
>
> Any help is appreciated.

First you need to settle on the business rules. Should 30 Feb translate
to 28 Feb most years, and 29 Feb leap years? Or should it translate to
March 2nd?

Once you have settled on the business rules, you may keep this in
mind if you ask for further assistance. When you ask with help with
a query it is often useful to include the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired output of that sample data.
o A short narrative.

That permits anyone who assists you post a tested solution.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

I've trying this in the Annual and Monthly case. I would try a union
all with the significant columns and use it as subquery for the final
output. I did not exhaustive try, so take it as is and good luck!

Diego Buendia
Barcelona Spain

create table T (
Date smalldatetime,
Task varchar(30),
ReminderInterval varchar(10),
ReminderDaysinAdvance int
)

insert T values ( '19570228', 'Joe Smith Birthday', 'Annual', 7 )
insert T values ( '20040130', 'Rent Due', 'Monthly', 4 )
insert T values ( '20040227', 'Dentist Appointment', 'None', 3 )
insert T values ( '20040125', 'Shrink Appointment', 'Weekly', 2 )
insert T values ( '20040125', 'Car Payment Due', 'Monthly', 4 )

declare @day smalldatetime
set @day = '20040226'

/*
case Annual: get the years in between, add to current day,
get difference in days and filter if less than reminderDaysInAdvance

*/

select
-- these columns are for demo only, you should filter them
datediff( year, date, @day ),
dateadd( year, datediff( year, date, @day ), date ),
datediff( day, @day, dateadd( year, datediff( year, date, @day ),
date ) ),

reminderdaysinadvance,

date, @day, task, year(date) as aniversary
from t
where
reminderinterval = 'Annual'
and
1 =
case when
datediff( day, @day, dateadd( year, datediff( year, date, @day ),
date ) )
between 0 And ReminderDaysInAdvance
then 1
else 0
end


/*
case Monthly
*/

select
datediff( month, date, @day ),
dateadd( month, datediff( month, date, @day ), date ),
datediff( day, @day, dateadd( month, datediff( month, date, @day ),
date ) ),

reminderdaysinadvance,

date, @day, task, year(date) as aniversary
from t
where
reminderinterval = 'Monthly'
and
1 =
case when

datediff( day, @day, dateadd( month, datediff( month, date, @day ),
date ) )
between 0 and ReminderDaysInAdvance

then 1
else 0
end

Lauren,
The keys to this problem are:
1. Let the database do the date math. It already knows what a valid date
is and what, for example, 1/30/2004 + one month is. Let it do that work.

2. Treat each reminder Interval as a different problem. Make each have
it's own SQL and put them together with a UNION.

3. Recognize that your SQL ends up comparing getdate() (ie: today) between
2 dates. For example, the monthly reminder would look like:

and getdate() between ThisMonthsReminder - DaysInAdvance
and ThisMonthsReminder

So, you need to first figure out what this month's reminder is (for monthly
reminders). This month's reminder is:

dateadd(mm, datediff(mm,reminder_date,getdate()), reminder_date)
- the datediff figures out how many months between today and my reminder
date.
- the dateadd adds that number of months to my reminder date to give me
ThisMonthsReminder

What I did to solve this is write a procedure which takes any date (not just
today) as an arguement and gives you the annual and monthly reminders. The
weekly and None Intervals should follow the same pattern.

Creating the tables and inserting the data were done like this:

create table tblMyTaskTable(reminder_date datetime,
task varchar(100),
reminder_interval varchar(30),
days_in_advance integer)
go
insert into tblMyTaskTable values('2/28/1957','Joe Smith
Birthday','Annual',7)
go
insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly','4')
go
insert into tblMyTaskTable values('2/27/2004','Dentist
Appointment','None','3')
go
insert into tblMyTaskTable values('1/25/2004','Shrink
Appointment','Weekly','2')
go
insert into tblMyTaskTable values('1/25/2004','Car Payment
Due','Monthly','4')
go


The procedure looks like:
create procedure get_reminders (@as_date datetime)
as
begin

declare @s_year char(4)
declare @s_date char(5)

set @s_date = substring(convert(varchar,@as_date,120),6,5)
set @s_year = substring(convert(varchar,@as_date,120),1,4)

print @s_date
print @s_year

select dateadd(yy,datediff(yy,reminder_date,@as_date),rem inder_date),task
from tblMytasktable
where reminder_interval = 'Annual'
and @as_date between

dateadd(dd,(-1)*days_in_advance,dateadd(yy,datediff(yy,reminder _date,@as_dat
e),reminder_date))
and dateadd(yy,datediff(yy,reminder_date,@as_date),rem inder_date)

union

select dateadd(mm,datediff(mm,reminder_date,@as_date),rem inder_date), task
from tblMytasktable
where reminder_interval = 'Monthly'
and @as_date between
dateadd(dd,(-1)*days_in_advance,dateadd(mm,datediff(mm,reminder _date,@as_dat
e),reminder_date) )
and dateadd(mm,datediff(mm,reminder_date,@as_date),rem inder_date)

end

call the procedure for 2/26/04 like this:

exec get_reminders '2004-02-26'

The biggest problem with this procedure and the main query is that it cannot
use an index on reminder_date. So, if you are using this with more than 200
or so reminders, it could be slow, depending on your hardware.

Let me know if you need help with the weekly and None interval sections of
the SQL. Also, you might find some help with SQL Server on our
www.TechnicalVideos.net site.

Best regards,
Chuck Conover
www.TechnicalVideos.net





"Lauren Quantrell" <laurenquantrell@hotmail.com> wrote in message
news:47e5bd72.0401241342.6931ae64@posting.google.c om...
> To further elaborate:
>
> tblMyTaskTable:
>
> Date Task ReminderInterval
> ReminderDaysinAdvance
> 2/28/1957 Joe Smith Birthday Annual 7
> 1/30/2004 Rent Due Monthly 4
> 2/27/2004 Dentist Appointment None 3
> 1/25/2004 Shrink Appointment Weekly 2
> 1/25/2004 Car Payment Due Monthly 4
>
> What I'm trying to produce is a form that shows this list when a user
> logs in on Feb. 26:
>
> Feb. 25 Shrink Appointment
> Feb. 27 Dentist Appointment
> Feb. 28 Joe Smith Birthday (1957)
> Feb. 30 Rent Due (except there's no Feb. 30 so I have to deal with
> that too!)
>
> (the car payment won't show up because today is Feb. 26 and the next
> reminder won't trigger until four days before the 25th of the next
> month.)
>
> This is proving to be very complex!
>
> Any help is appreciated.
> lq
>
>
>
>
>
>
>
>
> Erland Sommarskog <sommar@algonet.se> wrote in message
news:<Xns947A9EA01D044Yazorman@127.0.0.1>...
> > Mystery Man (PromisedOyster@hotmail.com) writes:
> > > I think the following clause is not correct:
> > >
> > > CONVERT(smalldatetime,str(DATEPART(Month, @DateNow)+1)
> >
> > Yes, taking out of context it is grossly incorrect, because there is a
> > right parathesis missing. This is the complete expression, reformatted
> > for legibility:
> >
> > CONVERT(smalldatetime,
> > str(DATEPART(Month, @DateNow) + 1) + '/' +
> > str(DATEPART(Day, tblMyEventTableName.TaskDateTime)) + '/'
+
> > str(DATEPART(Year, @DateNow)),
> > 101)
> >
> > I did the same reflection as you, but I tried in QA to see what it
> > would actually return. It took a while to grasp that syntax error...
> >
> > Anway, not even the complete expression is good, because it does not
> > work for dates like 2004-01-30.