Problem using Convert on dates

**Erland Sommarskog** · Jul 20 '05, 02:39 AM

Re: Problem using Convert on dates

Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> 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,tbl MyEventTableNam e.ReminderDays, @DateNow) Between
> CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1) + '/' +
> str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
> str(DATEPART(Ye ar, @DateNow)),101) AND
> tblMyEventTable Name.Recurrence End)[/color]

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 blMyEventTableN ame.ReminderDay s, 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

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Mystery Man** · Jul 20 '05, 02:39 AM

Re: Problem using Convert on dates

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns947A2B 67E4B1Yazorman@ 127.0.0.1>...[color=blue]
> Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=green]
> > 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,tbl MyEventTableNam e.ReminderDays, @DateNow) Between
> > CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1) + '/' +
> > str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
> > str(DATEPART(Ye ar, @DateNow)),101) AND
> > tblMyEventTable Name.Recurrence End)[/color]
>
> 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 blMyEventTableN ame.ReminderDay s, the expression above will not
> use that index, since the column is part of an expression.[/color]

I think the following clause is not correct:

CONVERT(smallda tetime,str(DATE PART(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(smallda tetime,str(DATE PART(Month, @DateNow)+1))
-- This works
select CONVERT(int,str (DATEPART(Month , @DateNow)+1))

**Erland Sommarskog** · Jul 20 '05, 02:39 AM

Re: Problem using Convert on dates

Mystery Man (PromisedOyster @hotmail.com) writes:[color=blue]
> I think the following clause is not correct:
>
> CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1)[/color]

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(smallda tetime,
str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
str(DATEPART(Ye ar, @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

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Lauren Quantrell** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

But the convert is being run on the whole section m/d/y:
CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1) + '/' +
str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
str(DATEPART(Ye ar, @DateNow)),101)

PromisedOyster@ hotmail.com (Mystery Man) wrote in message news:<87c81238. 0401240245.253f 0a69@posting.go ogle.com>...[color=blue]
> Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns947A2B 67E4B1Yazorman@ 127.0.0.1>...[color=green]
> > Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=darkred]
> > > 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,tbl MyEventTableNam e.ReminderDays, @DateNow) Between
> > > CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1) + '/' +
> > > str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
> > > str(DATEPART(Ye ar, @DateNow)),101) AND
> > > tblMyEventTable Name.Recurrence End)[/color]
> >
> > 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 blMyEventTableN ame.ReminderDay s, the expression above will not
> > use that index, since the column is part of an expression.[/color]
>
>
> I think the following clause is not correct:
>
> CONVERT(smallda tetime,str(DATE PART(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(smallda tetime,str(DATE PART(Month, @DateNow)+1))
> -- This works
> select CONVERT(int,str (DATEPART(Month , @DateNow)+1))[/color]

**Lauren Quantrell** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

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:<Xns947A9E A01D044Yazorman @127.0.0.1>...[color=blue]
> Mystery Man (PromisedOyster @hotmail.com) writes:[color=green]
> > I think the following clause is not correct:
> >
> > CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1)[/color]
>
> 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(smallda tetime,
> str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
> str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
> str(DATEPART(Ye ar, @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.[/color]

**Lauren Quantrell** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

To further elaborate:

tblMyTaskTable:

Date Task ReminderInterva l
ReminderDaysinA dvance
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:<Xns947A9E A01D044Yazorman @127.0.0.1>...[color=blue]
> Mystery Man (PromisedOyster @hotmail.com) writes:[color=green]
> > I think the following clause is not correct:
> >
> > CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1)[/color]
>
> 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(smallda tetime,
> str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
> str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
> str(DATEPART(Ye ar, @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.[/color]

**Erland Sommarskog** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> 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.[/color]

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

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Diego Buendia** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

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),
ReminderInterva l varchar(10),
ReminderDaysinA dvance 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 reminderDaysInA dvance

*/

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 ) ),

reminderdaysina dvance,

date, @day, task, year(date) as aniversary
from t
where
reminderinterva l = 'Annual'
and
1 =
case when
datediff( day, @day, dateadd( year, datediff( year, date, @day ),
date ) )
between 0 And ReminderDaysInA dvance
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 ) ),

reminderdaysina dvance,

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

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

then 1
else 0
end

**Chuck Conover** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

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 ThisMonthsRemin der - DaysInAdvance
and ThisMonthsRemin der

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,rem inder_date,getd ate()), 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
ThisMonthsRemin der

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_interv al varchar(30),
days_in_advance integer)
go
insert into tblMyTaskTable values('2/28/1957','Joe Smith
Birthday','Annu al',7)
go
insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly', '4')
go
insert into tblMyTaskTable values('2/27/2004','Dentist
Appointment','N one','3')
go
insert into tblMyTaskTable values('1/25/2004','Shrink
Appointment','W eekly','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(conve rt(varchar,@as_ date,120),6,5)
set @s_year = substring(conve rt(varchar,@as_ date,120),1,4)

print @s_date
print @s_year

select dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date ),task
from tblMytasktable
where reminder_interv al = 'Annual'
and @as_date between

dateadd(dd,(-1)*days_in_adva nce,dateadd(yy, datediff(yy,rem inder_date,@as_ dat
e),reminder_dat e))
and dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date )

union

select dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_date ), task
from tblMytasktable
where reminder_interv al = 'Monthly'
and @as_date between
dateadd(dd,(-1)*days_in_adva nce,dateadd(mm, datediff(mm,rem inder_date,@as_ dat
e),reminder_dat e) )
and dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_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

https://www.TechnicalVideos.net

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 401241342.6931a e64@posting.goo gle.com...[color=blue]
> To further elaborate:
>
> tblMyTaskTable:
>
> Date Task ReminderInterva l
> ReminderDaysinA dvance
> 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[/color]
news:<Xns947A9E A01D044Yazorman @127.0.0.1>...[color=blue][color=green]
> > Mystery Man (PromisedOyster @hotmail.com) writes:[color=darkred]
> > > I think the following clause is not correct:
> > >
> > > CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1)[/color]
> >
> > 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(smallda tetime,
> > str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
> > str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/'[/color][/color]
+[color=blue][color=green]
> > str(DATEPART(Ye ar, @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.[/color][/color]

**Lauren Quantrell** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

Chuck,
Thank you so much for the solution!
I modified your SP and included weekly and non-recurring events and it
works great.
I notice in your SP you included:
[color=blue]
> declare @s_year char(4)
> declare @s_date char(5)
>
> set @s_date = substring(conve rt(varchar,@as_ date,120),6,5)
> set @s_year = substring(conve rt(varchar,@as_ date,120),1,4)[/color]

....though you don't use it in the code.

A question, I haven't used Union in SPs before. How can I do an ORDER
BY for the whole result. I have four select statements joined with the
unions.
Thanks,
lq

"Chuck Conover" <cconover@comms peed.net> wrote in message news:<107506373 0.111466@news.c ommspeed.net>.. .[color=blue]
> 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 ThisMonthsRemin der - DaysInAdvance
> and ThisMonthsRemin der
>
> 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,rem inder_date,getd ate()), 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
> ThisMonthsRemin der
>
> 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_interv al varchar(30),
> days_in_advance integer)
> go
> insert into tblMyTaskTable values('2/28/1957','Joe Smith
> Birthday','Annu al',7)
> go
> insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly', '4')
> go
> insert into tblMyTaskTable values('2/27/2004','Dentist
> Appointment','N one','3')
> go
> insert into tblMyTaskTable values('1/25/2004','Shrink
> Appointment','W eekly','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(conve rt(varchar,@as_ date,120),6,5)
> set @s_year = substring(conve rt(varchar,@as_ date,120),1,4)
>
> print @s_date
> print @s_year
>
> select dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date ),task
> from tblMytasktable
> where reminder_interv al = 'Annual'
> and @as_date between
>
> dateadd(dd,(-1)*days_in_adva nce,dateadd(yy, datediff(yy,rem inder_date,@as_ dat
> e),reminder_dat e))
> and dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date )
>
> union
>
> select dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_date ), task
> from tblMytasktable
> where reminder_interv al = 'Monthly'
> and @as_date between
> dateadd(dd,(-1)*days_in_adva nce,dateadd(mm, datediff(mm,rem inder_date,@as_ dat
> e),reminder_dat e) )
> and dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_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" <laurenquantrel l@hotmail.com> wrote in message
> news:47e5bd72.0 401241342.6931a e64@posting.goo gle.com...[color=green]
> > To further elaborate:
> >
> > tblMyTaskTable:
> >
> > Date Task ReminderInterva l
> > ReminderDaysinA dvance
> > 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[/color]
> news:<Xns947A9E A01D044Yazorman @127.0.0.1>...[color=green][color=darkred]
> > > Mystery Man (PromisedOyster @hotmail.com) writes:
> > > > I think the following clause is not correct:
> > > >
> > > > CONVERT(smallda tetime,str(DATE PART(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(smallda tetime,
> > > str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
> > > str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/'[/color][/color]
> +[color=green][color=darkred]
> > > str(DATEPART(Ye ar, @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.[/color][/color][/color]

**Lauren Quantrell** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

Diego,
Thanks for pointing me in the right direction on this.
lq

dbuendiab@yahoo .es (Diego Buendia) wrote in message news:<71e97616. 0401251129.43ff fb13@posting.go ogle.com>...[color=blue]
> 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),
> ReminderInterva l varchar(10),
> ReminderDaysinA dvance 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 reminderDaysInA dvance
>
> */
>
> 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 ) ),
>
> reminderdaysina dvance,
>
> date, @day, task, year(date) as aniversary
> from t
> where
> reminderinterva l = 'Annual'
> and
> 1 =
> case when
> datediff( day, @day, dateadd( year, datediff( year, date, @day ),
> date ) )
> between 0 And ReminderDaysInA dvance
> 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 ) ),
>
> reminderdaysina dvance,
>
> date, @day, task, year(date) as aniversary
> from t
> where
> reminderinterva l = 'Monthly'
> and
> 1 =
> case when
>
> datediff( day, @day, dateadd( month, datediff( month, date, @day ),
> date ) )
> between 0 and ReminderDaysInA dvance
>
> then 1
> else 0
> end[/color]

**Chuck Conover** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

Lauren,
Truthfully, it took me about an hour to figure out this solution, given
the complexity. So, it went thru many changes. @s_year and @s_date were
used in an earlier version.
To do "order by" when using a union, specify the column number instead
of the column name like:

order by 1 <-- to order by the first column
order by 2,1 <-- to order by the second column, then the first column

Best regards,
Chuck Conover

https://www.TechnicalVideos.net

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 401270819.19e60 68@posting.goog le.com...[color=blue]
> Chuck,
> Thank you so much for the solution!
> I modified your SP and included weekly and non-recurring events and it
> works great.
> I notice in your SP you included:
>[color=green]
> > declare @s_year char(4)
> > declare @s_date char(5)
> >
> > set @s_date = substring(conve rt(varchar,@as_ date,120),6,5)
> > set @s_year = substring(conve rt(varchar,@as_ date,120),1,4)[/color]
>
> ...though you don't use it in the code.
>
> A question, I haven't used Union in SPs before. How can I do an ORDER
> BY for the whole result. I have four select statements joined with the
> unions.
> Thanks,
> lq
>
>
> "Chuck Conover" <cconover@comms peed.net> wrote in message[/color]
news:<107506373 0.111466@news.c ommspeed.net>.. .[color=blue][color=green]
> > Lauren,
> > The keys to this problem are:
> > 1. Let the database do the date math. It already knows what a valid[/color][/color]
date[color=blue][color=green]
> > is and what, for example, 1/30/2004 + one month is. Let it do that[/color][/color]
work.[color=blue][color=green]
> >
> > 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)[/color][/color]
between[color=blue][color=green]
> > 2 dates. For example, the monthly reminder would look like:
> >
> > and getdate() between ThisMonthsRemin der - DaysInAdvance
> > and ThisMonthsRemin der
> >
> > So, you need to first figure out what this month's reminder is (for[/color][/color]
monthly[color=blue][color=green]
> > reminders). This month's reminder is:
> >
> > dateadd(mm, datediff(mm,rem inder_date,getd ate()), reminder_date)
> > - the datediff figures out how many months between today and my[/color][/color]
reminder[color=blue][color=green]
> > date.
> > - the dateadd adds that number of months to my reminder date to give me
> > ThisMonthsRemin der
> >
> > What I did to solve this is write a procedure which takes any date (not[/color][/color]
just[color=blue][color=green]
> > today) as an arguement and gives you the annual and monthly reminders.[/color][/color]
The[color=blue][color=green]
> > 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_interv al varchar(30),
> > days_in_advance integer)
> > go
> > insert into tblMyTaskTable values('2/28/1957','Joe Smith
> > Birthday','Annu al',7)
> > go
> > insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly', '4')
> > go
> > insert into tblMyTaskTable values('2/27/2004','Dentist
> > Appointment','N one','3')
> > go
> > insert into tblMyTaskTable values('1/25/2004','Shrink
> > Appointment','W eekly','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(conve rt(varchar,@as_ date,120),6,5)
> > set @s_year = substring(conve rt(varchar,@as_ date,120),1,4)
> >
> > print @s_date
> > print @s_year
> >
> > select[/color][/color]
dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date ),task[color=blue][color=green]
> > from tblMytasktable
> > where reminder_interv al = 'Annual'
> > and @as_date between
> >
> >[/color][/color]
dateadd(dd,(-1)*days_in_adva nce,dateadd(yy, datediff(yy,rem inder_date,@as_ dat[color=blue][color=green]
> > e),reminder_dat e))
> > and dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date )
> >
> > union
> >
> > select dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_date ),[/color][/color]
task[color=blue][color=green]
> > from tblMytasktable
> > where reminder_interv al = 'Monthly'
> > and @as_date between
> >[/color][/color]
dateadd(dd,(-1)*days_in_adva nce,dateadd(mm, datediff(mm,rem inder_date,@as_ dat[color=blue][color=green]
> > e),reminder_dat e) )
> > and dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_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[/color][/color]
cannot[color=blue][color=green]
> > use an index on reminder_date. So, if you are using this with more than[/color][/color]
200[color=blue][color=green]
> > 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[/color][/color]
of[color=blue][color=green]
> > 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" <laurenquantrel l@hotmail.com> wrote in message
> > news:47e5bd72.0 401241342.6931a e64@posting.goo gle.com...[color=darkred]
> > > To further elaborate:
> > >
> > > tblMyTaskTable:
> > >
> > > Date Task ReminderInterva l
> > > ReminderDaysinA dvance
> > > 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[/color]
> > news:<Xns947A9E A01D044Yazorman @127.0.0.1>...[color=darkred]
> > > > Mystery Man (PromisedOyster @hotmail.com) writes:
> > > > > I think the following clause is not correct:
> > > > >
> > > > > CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1)
> > > >
> > > > Yes, taking out of context it is grossly incorrect, because there is[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > > right parathesis missing. This is the complete expression,[/color][/color][/color]
reformatted[color=blue][color=green][color=darkred]
> > > > for legibility:
> > > >
> > > > CONVERT(smallda tetime,
> > > > str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
> > > > str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) +[/color][/color][/color]
'/'[color=blue][color=green]
> > +[color=darkred]
> > > > str(DATEPART(Ye ar, @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.[/color][/color][/color]

**Erland Sommarskog** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=blue]
> A question, I haven't used Union in SPs before. How can I do an ORDER
> BY for the whole result. I have four select statements joined with the
> unions.[/color]

Here is an example:

SELECT CompanyName
FROM Northwind..Cust omers
UNION
SELECT ProductName
FROM Northwind..Prod ucts
ORDER BY CompanyName

The ORDER BY clause comes after all SELECT statements, and you use
the column names of the first SELECT. Of course, you can use column
numbers, as suggested by Chuck.

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

Books Online for SQL Server SP3 at

Microsoft SQL Server

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

**Lauren Quantrell** · Jul 20 '05, 02:40 AM

Re: Problem using Convert on dates

Thanks for all the help. The reminder calendar is working slendidly
now. (Unfortunately it already worked splendidly on an MS Access2K.MDB
with VBA before with many hours of trial and error. So much to redo in
SSQL Server...)
I made a brain dead mistake on the Order By and now it works, but it
leads me to another question.
Since the SP is many lines long, it seems very silly to copy the whole
SP into a another SP just to change the sort order. Is there a
simpler solution?
I suppose I could pass it a tinyint and a nvarchar parameter to handle
the sorting:
@myColumn tinyint, @myOrder nvarchar(4) = null
....
ORDER BY @myColumn + ' ' + @myOrder
But this could get very complex with multiple column sorting
variations.
Is there a better way?

Thanks again for all the help!
lq

"Chuck Conover" <cconover@comms peed.net> wrote in message news:<107522254 2.653551@news.c ommspeed.net>.. .[color=blue]
> Lauren,
> Truthfully, it took me about an hour to figure out this solution, given
> the complexity. So, it went thru many changes. @s_year and @s_date were
> used in an earlier version.
> To do "order by" when using a union, specify the column number instead
> of the column name like:
>
> order by 1 <-- to order by the first column
> order by 2,1 <-- to order by the second column, then the first column
>
> Best regards,
> Chuck Conover
> www.TechnicalVideos.net
>
>
>
> "Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
> news:47e5bd72.0 401270819.19e60 68@posting.goog le.com...[color=green]
> > Chuck,
> > Thank you so much for the solution!
> > I modified your SP and included weekly and non-recurring events and it
> > works great.
> > I notice in your SP you included:
> >[color=darkred]
> > > declare @s_year char(4)
> > > declare @s_date char(5)
> > >
> > > set @s_date = substring(conve rt(varchar,@as_ date,120),6,5)
> > > set @s_year = substring(conve rt(varchar,@as_ date,120),1,4)[/color]
> >
> > ...though you don't use it in the code.
> >
> > A question, I haven't used Union in SPs before. How can I do an ORDER
> > BY for the whole result. I have four select statements joined with the
> > unions.
> > Thanks,
> > lq
> >
> >
> > "Chuck Conover" <cconover@comms peed.net> wrote in message[/color]
> news:<107506373 0.111466@news.c ommspeed.net>.. .[color=green][color=darkred]
> > > Lauren,
> > > The keys to this problem are:
> > > 1. Let the database do the date math. It already knows what a valid[/color][/color]
> date[color=green][color=darkred]
> > > is and what, for example, 1/30/2004 + one month is. Let it do that[/color][/color]
> work.[color=green][color=darkred]
> > >
> > > 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)[/color][/color]
> between[color=green][color=darkred]
> > > 2 dates. For example, the monthly reminder would look like:
> > >
> > > and getdate() between ThisMonthsRemin der - DaysInAdvance
> > > and ThisMonthsRemin der
> > >
> > > So, you need to first figure out what this month's reminder is (for[/color][/color]
> monthly[color=green][color=darkred]
> > > reminders). This month's reminder is:
> > >
> > > dateadd(mm, datediff(mm,rem inder_date,getd ate()), reminder_date)
> > > - the datediff figures out how many months between today and my[/color][/color]
> reminder[color=green][color=darkred]
> > > date.
> > > - the dateadd adds that number of months to my reminder date to give me
> > > ThisMonthsRemin der
> > >
> > > What I did to solve this is write a procedure which takes any date (not[/color][/color]
> just[color=green][color=darkred]
> > > today) as an arguement and gives you the annual and monthly reminders.[/color][/color]
> The[color=green][color=darkred]
> > > 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_interv al varchar(30),
> > > days_in_advance integer)
> > > go
> > > insert into tblMyTaskTable values('2/28/1957','Joe Smith
> > > Birthday','Annu al',7)
> > > go
> > > insert into tblMyTaskTable values('1/30/2004','Rent Due','Monthly', '4')
> > > go
> > > insert into tblMyTaskTable values('2/27/2004','Dentist
> > > Appointment','N one','3')
> > > go
> > > insert into tblMyTaskTable values('1/25/2004','Shrink
> > > Appointment','W eekly','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(conve rt(varchar,@as_ date,120),6,5)
> > > set @s_year = substring(conve rt(varchar,@as_ date,120),1,4)
> > >
> > > print @s_date
> > > print @s_year
> > >
> > > select[/color][/color]
> dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date ),task[color=green][color=darkred]
> > > from tblMytasktable
> > > where reminder_interv al = 'Annual'
> > > and @as_date between
> > >
> > >[/color][/color]
> dateadd(dd,(-1)*days_in_adva nce,dateadd(yy, datediff(yy,rem inder_date,@as_ dat[color=green][color=darkred]
> > > e),reminder_dat e))
> > > and dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date )
> > >
> > > union
> > >
> > > select dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_date ),[/color][/color]
> task[color=green][color=darkred]
> > > from tblMytasktable
> > > where reminder_interv al = 'Monthly'
> > > and @as_date between
> > >[/color][/color]
> dateadd(dd,(-1)*days_in_adva nce,dateadd(mm, datediff(mm,rem inder_date,@as_ dat[color=green][color=darkred]
> > > e),reminder_dat e) )
> > > and dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_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[/color][/color]
> cannot[color=green][color=darkred]
> > > use an index on reminder_date. So, if you are using this with more than[/color][/color]
> 200[color=green][color=darkred]
> > > 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[/color][/color]
> of[color=green][color=darkred]
> > > 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" <laurenquantrel l@hotmail.com> wrote in message
> > > news:47e5bd72.0 401241342.6931a e64@posting.goo gle.com...
> > > > To further elaborate:
> > > >
> > > > tblMyTaskTable:
> > > >
> > > > Date Task ReminderInterva l
> > > > ReminderDaysinA dvance
> > > > 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[/color][/color]
> news:<Xns947A9E A01D044Yazorman @127.0.0.1>...[color=green][color=darkred]
> > > > > Mystery Man (PromisedOyster @hotmail.com) writes:
> > > > > > I think the following clause is not correct:
> > > > > >
> > > > > > CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1)
> > > > >
> > > > > Yes, taking out of context it is grossly incorrect, because there is[/color][/color]
> a[color=green][color=darkred]
> > > > > right parathesis missing. This is the complete expression,[/color][/color]
> reformatted[color=green][color=darkred]
> > > > > for legibility:
> > > > >
> > > > > CONVERT(smallda tetime,
> > > > > str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
> > > > > str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) +[/color][/color]
> '/'
> +[color=green][color=darkred]
> > > > > str(DATEPART(Ye ar, @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.[/color][/color][/color]

Problem using Convert on dates

Problem using Convert on dates

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment