Problem using Convert on dates

[Lauren Quantrell]

Re: Problem using Convert on dates

Erland,
I had a brain spaz on that one. Sorry. But thanks for the reply.
lq

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns947DF2 44CC05BYazorman @127.0.0.1>...[color=blue]
> Lauren Quantrell (laurenquantrel l@hotmail.com) writes:[color=green]
> > 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.[/color]

[Lauren Quantrell]

Re: Problem using Convert on dates

Chuck,
So what if I want to include a recurring pattern for Weekday reminders?
I see T-SQL "dw" would I just substitute that for "ww" ???
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]

[Lauren Quantrell]

Re: Problem using Convert on dates

Sorry, I keep answering my own questions...
For weekday:
DatePart(dw,(@a s_date) Between 2 and 6

"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]

[Chuck Conover]

Re: Problem using Convert on dates

Lauren,
Now you're getting complicated. Give me an example of what you're
looking for and I might be able to work up a solution.

Best regards,
Chuck

https://www.TechnicalVideos.net

"Lauren Quantrell" <laurenquantrel l@hotmail.com> wrote in message
news:47e5bd72.0 401271845.e4d9b ff@posting.goog le.com...[color=blue]
> Chuck,
> So what if I want to include a recurring pattern for Weekday reminders?
> I see T-SQL "dw" would I just substitute that for "ww" ???
> lq
>
>
> "Chuck Conover" <cconover@comms peed.net> wrote in message[/color]
news:<107522254 2.653551@news.c ommspeed.net>.. .[color=blue][color=green]
> > Lauren,
> > Truthfully, it took me about an hour to figure out this solution,[/color][/color]
given[color=blue][color=green]
> > the complexity. So, it went thru many changes. @s_year and @s_date[/color][/color]
were[color=blue][color=green]
> > used in an earlier version.
> > To do "order by" when using a union, specify the column number[/color][/color]
instead[color=blue][color=green]
> > 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=darkred]
> > > 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:
> > >
> > > > 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)
> > >
> > > ...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=darkred]
> > > > Lauren,
> > > > The keys to this problem are:
> > > > 1. Let the database do the date math. It already knows what a[/color][/color][/color]
valid[color=blue][color=green]
> > date[color=darkred]
> > > > is and what, for example, 1/30/2004 + one month is. Let it do that[/color]
> > work.[color=darkred]
> > > >
> > > > 2. Treat each reminder Interval as a different problem. Make each[/color][/color][/color]
have[color=blue][color=green][color=darkred]
> > > > it's own SQL and put them together with a UNION.
> > > >
> > > > 3. Recognize that your SQL ends up comparing getdate() (ie: today)[/color]
> > between[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]
> > monthly[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]
> > reminder[color=darkred]
> > > > date.
> > > > - the dateadd adds that number of months to my reminder date to[/color][/color][/color]
give me[color=blue][color=green][color=darkred]
> > > > ThisMonthsRemin der
> > > >
> > > > What I did to solve this is write a procedure which takes any date[/color][/color][/color]
(not[color=blue][color=green]
> > just[color=darkred]
> > > > today) as an arguement and gives you the annual and monthly[/color][/color][/color]
reminders.[color=blue][color=green]
> > The[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[/color][/color][/color]
Due','Monthly', '4')[color=blue][color=green][color=darkred]
> > > > 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]
> > dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date ),task[color=darkred]
> > > > from tblMytasktable
> > > > where reminder_interv al = 'Annual'
> > > > and @as_date between
> > > >
> > > >[/color]
> >[/color][/color]
dateadd(dd,(-1)*days_in_adva nce,dateadd(yy, datediff(yy,rem inder_date,@as_ dat[color=blue][color=green][color=darkred]
> > > > e),reminder_dat e))
> > > > and[/color][/color][/color]
dateadd(yy,date diff(yy,reminde r_date,@as_date ),reminder_date )[color=blue][color=green][color=darkred]
> > > >
> > > > union
> > > >
> > > > select[/color][/color][/color]
dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_date ),[color=blue][color=green]
> > task[color=darkred]
> > > > from tblMytasktable
> > > > where reminder_interv al = 'Monthly'
> > > > and @as_date between
> > > >[/color]
> >[/color][/color]
dateadd(dd,(-1)*days_in_adva nce,dateadd(mm, datediff(mm,rem inder_date,@as_ dat[color=blue][color=green][color=darkred]
> > > > e),reminder_dat e) )
> > > > and[/color][/color][/color]
dateadd(mm,date diff(mm,reminde r_date,@as_date ),reminder_date )[color=blue][color=green][color=darkred]
> > > >
> > > > 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[/color][/color][/color]
it[color=blue][color=green]
> > cannot[color=darkred]
> > > > use an index on reminder_date. So, if you are using this with more[/color][/color][/color]
than[color=blue][color=green]
> > 200[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[/color][/color][/color]
sections[color=blue][color=green]
> > of[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[/color][/color][/color]
user[color=blue][color=green][color=darkred]
> > > > > 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[/color][/color][/color]
with[color=blue][color=green][color=darkred]
> > > > > that too!)
> > > > >
> > > > > (the car payment won't show up because today is Feb. 26 and the[/color][/color][/color]
next[color=blue][color=green][color=darkred]
> > > > > 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[/color][/color][/color]
there is[color=blue][color=green]
> > a[color=darkred]
> > > > > > right parathesis missing. This is the complete expression,[/color]
> > reformatted[color=darkred]
> > > > > > for legibility:
> > > > > >
> > > > > > CONVERT(smallda tetime,
> > > > > > str(DATEPART(Mo nth, @DateNow) + 1) + '/' +
> > > > > > str(DATEPART(Da y,[/color][/color][/color]
tblMyEventTable Name.TaskDateTi me)) +[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[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> > > > > > would actually return. It took a while to grasp that syntax[/color][/color][/color]
error...[color=blue][color=green][color=darkred]
> > > > > >
> > > > > > Anway, not even the complete expression is good, because it does[/color][/color][/color]
not[color=blue][color=green][color=darkred]
> > > > > > work for dates like 2004-01-30.[/color][/color][/color]

[Erland Sommarskog]

Re: Problem using Convert on dates

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

I don't know what sort orders you are looking for, but the above ORDER BY
clause is equal to no ORDER BY clause at all, because you sort by a constant
value.

The standard way of dynamic sorting in T-SQL is to use a case expression:

CASE @mycolumn
WHEN 1 THEN this_col
WHEN 2 THEN that_col
WHEN 3 THEN that_col_other_ there
END

But this only works if all columns are of the same data type, since the
CASE expression has a fixed data type determined by some rules I don't
recall right now.

This can be addressed with:

ORDER BY CASE @mycolumn WHEN 1 THEN date_col ELSE NULL END,
CASE @mycolumn WHEN 2 THEN str_col ELSE NULL END,
CASE @mycolumn WHEN 3 THEN int_col ELSE NULL EMD

And yes, if you have very many columns that you want to permit sorting
on, this can become very complex. One alternative is do to the sorting
client-side. This had the advantage that you can rearrange the data
for the user, without doing a server round-trip.

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

Books Online for SQL Server SP3 at

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