473,836 Members | 1,503 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem using Convert on dates

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)

(tblMyEventTabl eName.ReminderD ays = 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 tblMyEventTable Name.Recurrence Pattern ='monthly' AND
DATEPART(d, @DateNow) <= DATEPART(d,tblM yEventTableName .TaskDateTime)
and tblMyEventTable Name.Recurrence End > @DateNow

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

THEN CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)) + '/' +
str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
str(DATEPART(Ye ar, @DateNow)),101) ELSE
CASE WHEN tblMyEventTable Name.Recurrence Pattern ='monthly' AND
DATEPART(d, @DateNow) > DATEPART(d,tblM yEventTableName .TaskDateTime)
and tblMyEventTable Name.Recurrence End > @DateNow

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

THEN CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)+1) + '/'
+ str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
str(DATEPART(Ye ar, @DateNow)),101) ELSE

/*RecurrencePatt ern is not set to monthly so just use the reminder
date*/
tblMyEventTable Name.TaskDateTi me
END
END
AS myReminderDate, tblMyEventTable Name.myTaskName
FROM
tblMyEventTable Name
WHERE

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

tblMyEventTable Name.Recurrence Pattern ='monthly'
AND
DATEPART(d, @DateNow) <= DATEPART(d,tblM yEventTableName .TaskDateTime)
AND
tblMyEventTable Name.Recurrence End > @DateNow
AND
DATEADD(Day,tbl MyEventTableNam e.ReminderDays, @DateNow) Between
CONVERT(smallda tetime,str(DATE PART(Month, @DateNow)) + '/' +
str(DATEPART(Da y, tblMyEventTable Name.TaskDateTi me)) + '/' +
str(DATEPART(Ye ar, @DateNow)),101) AND
tblMyEventTable Name.Recurrence End)

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

tblMyEventTable Name.Recurrence Pattern ='monthly'
AND
DATEPART(d, @DateNow) > DATEPART(d,tblM yEventTableName .TaskDateTime)
AND
tblMyEventTable Name.Recurrence End > @DateNow
AND
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)
Jul 20 '05
19 7302
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" <cc******@comms peed.net> wrote in message news:<10******* ********@news.c ommspeed.net>.. . 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" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@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 <so****@algonet .se> wrote in message

news:<Xn******* *************** @127.0.0.1>...
Mystery Man (Pr************ @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)) + '/' + 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.

Jul 20 '05 #11
Diego,
Thanks for pointing me in the right direction on this.
lq

db*******@yahoo .es (Diego Buendia) wrote in message news:<71******* *************** ****@posting.go ogle.com>...
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

Jul 20 '05 #12
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** **@posting.goog le.com...
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" <cc******@comms peed.net> wrote in message

news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@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 <so****@algonet .se> wrote in message

news:<Xn******* *************** @127.0.0.1>...
> Mystery Man (Pr************ @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)) +
'/' +
> 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.

Jul 20 '05 #13
Lauren Quantrell (la************ *@hotmail.com) writes:
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.


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #14
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" <cc******@comms peed.net> wrote in message news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** **@posting.goog le.com...
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" <cc******@comms peed.net> wrote in message

news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@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 <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>... > > Mystery Man (Pr************ @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)) + '/'
+ > > 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.

Jul 20 '05 #15
Erland,
I had a brain spaz on that one. Sorry. But thanks for the reply.
lq

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...
Lauren Quantrell (la************ *@hotmail.com) writes:
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.


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.

Jul 20 '05 #16
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" <cc******@comms peed.net> wrote in message news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** **@posting.goog le.com...
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" <cc******@comms peed.net> wrote in message

news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@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 <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>... > > Mystery Man (Pr************ @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)) + '/'
+ > > 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.

Jul 20 '05 #17
Sorry, I keep answering my own questions...
For weekday:
DatePart(dw,(@a s_date) Between 2 and 6

"Chuck Conover" <cc******@comms peed.net> wrote in message news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** **@posting.goog le.com...
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" <cc******@comms peed.net> wrote in message

news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** ***@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 <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>... > > Mystery Man (Pr************ @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)) + '/'
+ > > 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.

Jul 20 '05 #18
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
www.TechnicalVideos.net

"Lauren Quantrell" <la************ *@hotmail.com> wrote in message
news:47******** *************** **@posting.goog le.com...
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" <cc******@comms peed.net> wrote in message

news:<10******* ********@news.c ommspeed.net>.. .
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" <la************ *@hotmail.com> wrote in message
news:47******** *************** **@posting.goog le.com...
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" <cc******@comms peed.net> wrote in message

news:<10******* ********@news.c ommspeed.net>.. .
> 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" <la************ *@hotmail.com> wrote in message
> news:47******** *************** ***@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 <so****@algonet .se> wrote in message

news:<Xn******* *************** @127.0.0.1>...
> > > Mystery Man (Pr************ @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)) + '/'
+
> > > 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.

Jul 20 '05 #19
Lauren Quantrell (la************ *@hotmail.com) writes:
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?


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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #20

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
17339
by: Raghu | last post by:
Hello... I am running into a problem while running a query..can some1 help.. this is the query : ************** SELECT * from Table S where S.dtDate1 BETWEEN dateadd(year,1,dateadd(month,-1,getdate())) AND dateadd(day,-1,(dateadd(month,1,dateadd(year,1,dateadd(month,-1,getdate()))))) *************** (first part of the date calculation comes out to be '2005-05-01' and
1
1972
by: php newbie | last post by:
Hello, I am trying to insert some date values into a table. I am using ODBC prepared statements to (potentially) improve performance. The statement syntax I am using is this: INSERT INTO date_tests (date_col) VALUES ( CONVERT(DATETIME, ?, 121) ) The table is defined as "CREATE TABLE date_tests (date_col DATETIME)". Then, I call this prepared statement in a loop to insert some date
2
1928
by: Giulio | last post by:
I make an import of a huge file txt table with headers at each page and date format as "20.12.2004" in order to use the data I first qo a query that exclude all line representing the headers repeated for each page. now that I have a txt table with several colums I convert back the comlumns in to the format I need to perform other table: I mainly have to convert text fields in to Dates and numbers.
2
1609
by: ed | last post by:
I read a similar post on the group : http://groups.google.co.uk/groups?q=.net+cast+string+date+valid+sql&start=10&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=eRvILbWECHA.1548%40tkmsftngp05&rnum=15 I'm having a similar problem. My set up is : windows 2000 sever (all regional settings set to british / UK) SQLserver 2000 : (user account has default lang set to british (UK)) ..net framework erm... 1.1.4322 i think.
3
6132
by: seegoon | last post by:
Hi to all. I have a small problem I hope someone can help me with. I am running a sql query to a csv file. The query searches for the total of a column between 2 dates. This is a copy of one of the query's: Dim SF_SMT As New OleDb.OleDbCommand("Select count(*) from fault_records.csv where F4 = '" & lblprod.Text & " ' " & " AND F8 AND F1 between #" & SelectproductForm.datestart & "# and #" & Now.Date &
12
29487
by: Assimalyst | last post by:
Hi, I have a working script that converts a dd/mm/yyyy text box date entry to yyyy/mm/dd and compares it to the current date, giving an error through an asp.net custom validator, it is as follows: function doDateCheckNow(source, args) { var oDate = document.getElementById(source.controltovalidate); // dd/mm/yyyy
13
10710
by: Jim Armstrong | last post by:
Hi all - This problem has been driving me crazy, and I'm hoping the answer is something stupid I am neglecting to see.... The procedure posted below is part of an Access/SQL database I have developed. Basically, the user would input a beginning and ending date, and the query goes and pulls records that meet the following criteria: 1. TradeDate is between beginning date and ending date
5
2611
by: iulian.ilea | last post by:
Is correct to have a varchar field and insert dates of type dd/mm/yyyy into it? I choose this method because I have an application that runs on more than one server. So, if I used a datetime field (MSSQL Server) it worked on my test machine. If I run the same application on another machine with different regional settings is not working. I tried with date_default_timezone_set to change timezone but is not changing. After...
2
5161
by: Ceebaby via AccessMonster.com | last post by:
Hi Folks I wondered if someone could point me in the right direction before I completely tear my hair out. I have a user selection form where options can be selected for a report. Users now want to also filter the options by date selections or not if they wish. I added to unbound text fields to input the start and end dates and inserted them into my str Where code. It was working fine until the these were added.
0
10840
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10546
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10588
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.