471,851 Members | 1,251 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,851 software developers and data experts.

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 2009
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
NeoPa
reply views Thread by NeoPa | last post: by
aboka
reply views Thread by aboka | last post: by

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.