Wrox Programmer Forums
|
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 18th, 2008, 06:50 AM
Registered User
 
Join Date: Apr 2008
Posts: 5
Thanks: 0
Thanked 1 Time in 1 Post
Default Pulling Records from Friday

Hello there,
I am doing a company intranet website and be able to show the Memo from last 3 days. But the problem is, if someone post the memo on Friday night and the company closed on the weekend then on Monday morning nobody see the memo from last Friday night because its past 3 days. So this is my SQL

SELECT * FROM memo WHERE xdate > date()-3 ORDER BY xdate DESC

How can I pull the Memo from every last Friday on Monday?
I using ASP with Ms Access on xdate field I filled in code <%=Date()%>
Please help!:(
thank you

 
Old July 18th, 2008, 08:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there...

there are function to calculate that is a given day is a weekday, and what particular weekday is.. did you google about it? I can't recall any particular one...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
 
Old July 18th, 2008, 02:19 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

I wrote this for SQL before I noticed you're working in Access.

Here is the SQL solution:

SELECT * FROM Memo WHERE xdate > getDate() - (SELECT CASE When (SELECT DATEPART(dw, GETDATE())) = 1 THEN 5 ELSE 3 END )

I'm guessing that the Access date() is somewhat equal to the SQL getDate() which just produces today's date. Then the SELECT CASE statement finds out if today is Monday and if it is sets the value to 5 else it's 3. I put 5 in there since you are missing two days, Saturday and Sunday.

So you'll have to find the function in Access equal to the DATEPART in SQL which can tell you what day today is and you'll be set. Even if you can't use the SELECT CASE in Access you can always set a variable to the value you want to subtract and then use it in your Access SQL statement.

Hope that helps.

Richard

 
Old July 18th, 2008, 03:11 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, first of all, do you really ONLY want to see Friday memos on Monday???

Don't you want to see 3 *business days* back???

That is,
on FRIDAY, you want to see memos from WEDNESDAY, THURSDAY, FRIDAY
on THURSDAY, you want to see memos from TUESDAY, WEDNESDAY, THURSDAY
on WEDNESDAY, you want to see memos from MONDAY, TUESDAY, WEDNESDAY
on TUESDAY, you want to see memos from FRIDAY, MONDAY, TUESDAY
on MONDAY, you want to see memos from THURSDAY, FRIDAY, MONDAY

Is *THAT* what you *REALLY* want??

If so:

SELECT * FROM [Memo]
WHERE xdate > IIF( WEEKDAY(DATE()) IN (2,3), DATE()-5, DATE()-3 )

WEEKDAY() returns 2 for Monday and 3 for Tuesday.

But if you really ONLY want to see FRIDAY when the day is MONDAY and you do *NOT* want to see FRIDAY when the day is TUESDAY, then:

SELECT * FROM [Memo]
WHERE xdate > IIF( WEEKDAY(DATE()) = 2, DATE()-4, DATE()-3 )

***********************

By the way, rstelma's answer for T-SQL is a little wrong. He uses SELECT inside his CASE WHEN and he only needs a scalar expression there. And he apparently mistakenly thinks that 1 is the weekday number for MONDAY. Nope. As with Access SQL, 1 is SUNDAY. So the T-SQL answer is:

SELECT * FROM Memo
WHERE xdate > GETDATE() - (CASE WHEN DATEPART(dw, GETDATE())) IN (2,3) THEN 5 ELSE 3 END )

or

SELECT * FROM Memo
WHERE xdate > GETDATE() - (CASE WHEN DATEPART(dw, GETDATE())) = 2 THEN 4 ELSE 3 END )

Depending on which actual results you are after.
 
Old July 18th, 2008, 03:52 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hi Old Pendant,

Yes, I suffered a little slippage on the day numbers. SELECT DATEPART(dw,getDate()) results in a 6, today is Friday.

And the SELECT CASE worked so I didn't know that was a little wrong.

I like your logic. I too would rather have the last three business days of memos, however, your T-SQL query is also a little wrong. There is an extra parenthesis after the DATEPART GETDATE.

Thanks for your post.

Regards,
Richard



 
Old July 18th, 2008, 04:08 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Sorry, didn't mean to imply the SELECT in there was "wrong". Just unnecessary.

As for the parens... That's what comes form typing in these silly little enhanced text areas. Sorry. Good eyes!
 
Old July 18th, 2008, 04:18 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hey, no apologies necessary. I understood what you meant. I'm actually glad you pointed that out and it wasn't the eyes. I ran the query.

I don't want to hijack this thread so I was wondering if you could take a look at this thread if you have any experience with log shipping.

http://p2p.wrox.com/topic.asp?TOPIC_ID=72808

Thank you,
Richard






Similar Threads
Thread Thread Starter Forum Replies Last Post
pulling data from webpage zeeonline XSLT 3 July 27th, 2006 03:04 PM
Pulling records from the database debjanib ASP.NET 1.0 and 1.1 Professional 2 April 17th, 2006 09:03 AM
Pulling Fonts from Word mruthven Pro VB.NET 2002/2003 0 November 13th, 2004 04:12 PM
Date query pulling records from yesterday Justine Access 5 June 27th, 2004 03:45 PM
only pulling out 1 record Adam H-W Classic ASP Basics 4 April 12th, 2004 07:50 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.