SQL Question: Date subtraction

mcs328 · 10-25-2002, 01:50 PM

How would you write a SQL statement to return rows between a specific date and that same date minus a user entered number of days.

So I wanted records from 12/25/2001 and 45 days prior. The date and the number of days is user entered.

Just thought I throw this question in here and then start hunting for answers elsewhere. It'll be good warm up brain exercises for those in the know

wga22 · 11-03-2002, 11:35 AM

often you can convert the time to an integer, which represents the number of milliseconds since a given system date, such as 1-1-1970. Subtract 45 * 24 * 36000 from this number and you will have the number of milliseconds from 1970 of your new date. Convert this number back to a date.

Otherwise you will be challenged by crossing over months/years/leap years. Java and Javascript both have a date object that would let you do this programmatically, so if your database wont do it, maybe a stored procedure will, or you can write a program.

javascript: http://www.w3schools.com/js/js_datetime.asp see getTime()

Java: http://java.sun.com/products/jdk/1.1...util.Date.html

http://javascript.miningco.com/libra...bldatemath.htm

10-25-2002, 01:50 PM	#1
mcs328 Admiral Join Date: Feb 2001 Location: Maryland Posts: 6,578	SQL Question: Date subtraction How would you write a SQL statement to return rows between a specific date and that same date minus a user entered number of days. So I wanted records from 12/25/2001 and 45 days prior. The date and the number of days is user entered. Just thought I throw this question in here and then start hunting for answers elsewhere. It'll be good warm up brain exercises for those in the know __________________

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

11-03-2002, 11:35 AM	#2
wga22 Lieutenant Join Date: May 2000 Location: Vienna, VA Posts: 212	often you can convert the time to an integer, which represents the number of milliseconds since a given system date, such as 1-1-1970. Subtract 45 * 24 * 36000 from this number and you will have the number of milliseconds from 1970 of your new date. Convert this number back to a date. Otherwise you will be challenged by crossing over months/years/leap years. Java and Javascript both have a date object that would let you do this programmatically, so if your database wont do it, maybe a stored procedure will, or you can write a program. javascript: http://www.w3schools.com/js/js_datetime.asp see getTime() Java: http://java.sun.com/products/jdk/1.1...util.Date.html http://javascript.miningco.com/libra...bldatemath.htm

User Name
Password
Remember Me?