Select date columns value which are older than 10 days from today?

Question

Select date columns value which are older than 10 days from today?

kaiitong

Grasshopper

Points: 16
More actions
March 15, 2008 at 12:56 pm

#188048

Hi, everyone. I'm working on my senior project.
I'm stucking on selecting a table.
A Librarian needs to know which borrowing is older than 10 days from today; which is out of limit of borrowing a book.
select * from borrow where borrow_date ........
Someone please help me out,. Thank you
PS, I'm new here. Hope to get a nice help :D:D

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

10 days BEFORE today or 10 days AFTER today, PAST or FUTURE?

Since this is a school assignment I will just give a hint. In SQL Server dates work just like math. You can lookup date functions in SQL Server Books on Line and find how to do date calculations.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Hi, everyone. I'm working on my senior project.

Cool! Which school?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Jeff Moden (3/15/2008)
Hi, everyone. I'm working on my senior project.
Cool! Which school?

I interview Drexel seniors all the time who have senior projects just like this.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Thanks, Barry... Which state is Drexel in and which major is this typically for?

I'd still like to know which school Kaiitong is going to...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Drexel is in Philadelphia, PA and these are mostly IT/IS majors (part of the Business Administration school).

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

kaiitong Grasshopper Points: 16 More actions · Answer 6

I'm sorry for unclear question i asked.

10 days BEFORE today or 10 days AFTER today, PAST or FUTURE?

It's past. It's like if today is 16/03/2008 so the result will be the days after 05/03/2008.

Anyway, thanks for the hints. :D. But I've tried all of that. It'd be really nice of you to give a little more hint. 😉

I'd still like to know which school Kaiitong is going to...

I'm from Thailand, I study in Computer Science Department. 😉

Still wating for some help

Thank you

Kaiitong

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Try this:

Select * From borrow Where borrow_date < DateAdd(dd, -10, GetDate() )

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

kaiitong Grasshopper Points: 16 More actions · Answer 8

rbarryyoung (3/15/2008)
Try this:
Select * From borrow Where borrow_date < DateAdd(dd, -10, GetDate() )

OMG, That's work.

Thank you so much.

:laugh:

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Great... Barry gets the "A".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

That'll definitely improve my GPA. 😉

Seriously though, I don't mind the initial stuff. Everybody needs good examples to get started.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

J-440512 SSCertifiable Points: 6285 More actions · Answer 11

Comments invited.

You could also calculate ONCE the "10 +" dates -- when crreating the record.

True, you would need an additional column (AARG! Thrid Normal Form Violation ! do NOT add columns which can be calculated from other columns).

This would however allow a simpler WHERE clause: WHERE "date" <= "10 + date". This would have a benefit of allowing the read-back queries to use indexes. Useful in large database queried multiple times.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

By the way Kaitong -

I'm hoping you're testing what's given to you, enough to understand it. There's a little twist you need to realize with what Barry gave you. Let's just say what Barry gave you is MATHEMATICALLY precise and correct, and may or may not be what the library needs.

Meaning - he gave you precisely what you asked for, which usually is not exactly what libraries want (for determining lateness).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

J (3/17/2008)
Comments invited.
You could also calculate ONCE the "10 +" dates -- when crreating the record.
True, you would need an additional column (AARG! Thrid Normal Form Violation ! do NOT add columns which can be calculated from other columns).
This would however allow a simpler WHERE clause: WHERE "date" <= "10 + date". This would have a benefit of allowing the read-back queries to use indexes. Useful in large database queried multiple times.

You are partially correct. During database design you go to 3rd or 4th normal form. There is a however, though. That however is adding back controlled data redundence for performance. If the system needs to store what could be computed as needed to improve overall performance, that is a design decision.

😎

J-440512 SSCertifiable Points: 6285 More actions · Answer 14

My thoghts exactly.

FOURTH normal form? I know what it is but is it REALLY used in the real world (performance issues).