Date Calculations...

[mene]

I have a field that contains date information, and sometimes time
information as well. I would like to be able to take that date and do a
calculation on it. Here are some examples of what is in the field:

01/12/2003 5:04:00 PM
24/11/2003
19/05/2003 6:30:00 AM

How can I take that date, then do a calculation like minus 5 days from the
date. I understand that I am to use the GETDATE() function, but below is
the SQL I have implemented.

SELECT Field1, Field2, Field3
FROM Table1
WHERE (convert(char(10),Field1) like convert(char(8), GETDATE()-5))

For some reason this works, and it will return results that occur on this
day, but it disregards the year. Now someone will probably ask "Why
convert, char(10), etc". To be honest, I do not know and I ended up
implementing it from some other Usenet posts that are out there. I was
trying to figure this out and I ended up with that working until I later
realized it was only caring about the day and month. Any ideas what I am
doing wrong here? I just want to return results that have the day being 5
minus the current day. I am not interested in time information.

Thanks if anyone can help, I am by far not experienced in SQL.

[Oscar Santiesteban Jr.]

Take a look at the dateadd function of sql. It will do what you want, just
supply the date.

Oscar...

"mene" <me**@mene.nope> wrote in message
news:cy*******************@read2.cgocable.net...

I have a field that contains date information, and sometimes time
information as well. I would like to be able to take that date and do a
calculation on it. Here are some examples of what is in the field:

01/12/2003 5:04:00 PM
24/11/2003
19/05/2003 6:30:00 AM

How can I take that date, then do a calculation like minus 5 days from the
date. I understand that I am to use the GETDATE() function, but below is
the SQL I have implemented.

SELECT Field1, Field2, Field3
FROM Table1
WHERE (convert(char(10),Field1) like convert(char(8), GETDATE()-5))

For some reason this works, and it will return results that occur on this
day, but it disregards the year. Now someone will probably ask "Why
convert, char(10), etc". To be honest, I do not know and I ended up
implementing it from some other Usenet posts that are out there. I was
trying to figure this out and I ended up with that working until I later
realized it was only caring about the day and month. Any ideas what I am
doing wrong here? I just want to return results that have the day being 5
minus the current day. I am not interested in time information.

Thanks if anyone can help, I am by far not experienced in SQL.