469,592 Members | 2,032 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,592 developers. It's quick & easy.

Date Calculations...

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.
Jul 20 '05 #1
1 1843
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.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Marc | last post: by
2 posts views Thread by androtech | last post: by
30 posts views Thread by Dr John Stockton | last post: by
11 posts views Thread by lduperval | last post: by
5 posts views Thread by Simon Dean | last post: by
3 posts views Thread by Glencannon4424 via AccessMonster.com | last post: by
8 posts views Thread by Charlie Brookhart | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.