SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Comparing Dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/17/2000 :  23:06:29  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
sandy writes "hello team, suppose i were to store a slew of future date/time values in a table.
now, as the actual date/time approaches some of my stored values, how can i select only records that are within say 15 minutes of the actual time through a query on lets say an asp page that refreshes itself every 15 minutes.

hope i can't stump u. [hope u can understand my question] thanks omnipotent 1s"
We hope you can't stump us either :)

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 11/29/2000 :  05:25:28  Show Profile  Reply with Quote
No Stump here. Try this.

OK simple enough, try this. I included the DATEDIFF table for the conversions. The ABS function looks forward and back 15 min. If you just want the positive numbers then ditch the ABS() function

create PROCEDURE spGet15()

SELECT * FROM TABLE
WHERE ABS(DATEDIFF(mi, DateField, GETDATE())) <=15






Syntax

DATEDIFF(datepart, startdate, enddate)

Arguments

datepart

Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms

Go to Top of Page

Anonymous
Starting Member

0 Posts

Posted - 03/19/2001 :  18:14:06  Show Profile  Reply with Quote
Comparing Date In Two Tables

I don't think this is a stumper, but I'm having trouble because it involves aggregate functions.

I have a list of username's, user_id's in one table (users), and another table with user_id's, date's (board). The 'users' to 'board' relationship is 1 to many. In other words, the user_id is the unique key the users table, and every time a date is added to the 'board' table, a user_id is included. What I want to do is create a list of user_id's that have a date in the 'board' table that's less than 30 days old. So I want to take the MAX(date) from board, and compare it to getDate() and see if the result is less than 30, and if it is, include that user_id in my list. You don't really need to mess with the users table unless you want more information like the username, so the query might be similiar to this (although I know this is incorrect.)

SELECT user_id
FROM board
WHERE DATEDIFF(day, MAX(TableDate), getdate()) <= 30
GROUP BY user_id

However, this doesn't work... SQL complains about having the aggregate function MAX() in the WHERE clause. So I'm not sure how to write the clause to create my list. I'm actually trying to make a count of active users, so my query actually looks more like this, and might clue you into why I'm having difficulties.

SELECT count(user_id) AS activeCount
FROM board
WHERE DATEDIFF(day, MAX(TableDate), getdate()) <= 30
GROUP BY user_id

Thanks!

Jason
http://commerce.vertex.net/simple/clubs

Go to Top of Page

narayan_kumar
Starting Member

2 Posts

Posted - 07/11/2001 :  14:21:11  Show Profile  Reply with Quote
This is a reply for Jason

SELECT COUNT(user_id) FROM (SELECT user_id, MAX(TableDate) MaxUserDate FROM board GROUP BY user_id) TempTable
WHERE DATEDIFF(day, MaxUserDate, getdate()) <= 30
Go to Top of Page

JustinBigelow
SQL Gigolo

USA
1157 Posts

Posted - 07/11/2001 :  14:42:39  Show Profile  Reply with Quote
Hmm, Jason posted on 3/19. The reply came four months later. Wonder if he's been waiting all this time?

Justin

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000