Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

dvannoy (MIS) (OP)
3 Dec 04 18:52
I am trying to get records returned by  Today + 30 days back.. GETDATE() -30 returns no records..

How can I use the GETDATE function and go back 30 days included in the query?

Any help would be appreciated

mrdenny (Programmer)
3 Dec 04 18:55
dateadd would be a better way to handle this.
dateadd(dd, -30, getdate()).  Check BOL for full syntax.

Denny

--Anything is possible.  All it takes is a little research. (Me)


http://www.mrdenny.com (My very old site)

ESquared (Programmer)
3 Dec 04 19:35
mrdenny,

Honestly there is small difference between the two methods, so use whichever turns your crank. I'd even hazard a guess that subtracting 30 might possibly be faster.

dvannoy,

Show us your WHERE clause.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

mrdenny (Programmer)
3 Dec 04 20:03
True, there is little difference between them.  However dateadd is a handy function to know in case he later wants to go back by months, or years when getdate()-x doesn't work.

Denny

--Anything is possible.  All it takes is a little research. (Me)


http://www.mrdenny.com (My very old site)

ESquared (Programmer)
3 Dec 04 20:08
No argument, there.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

dvannoy (MIS) (OP)
6 Dec 04 11:37
I am doing an INNER JOIN with this query(not that it matters) but simple Where..

WHERE TABLE.DATE = GETDATE() -30
AND BLAAA

The date has time with it..

If I do  WHERE TABLE.DATE > '11/01/2004'

it works.

SQLBill (MIS)
6 Dec 04 13:13
First you are asking for two different things.

This:

WHERE TABLE.DATE = GETDATE() -30

asks for any data where Table.Date EQUALS 2004-11-06 18:06:37.050

This however:

WHERE TABLE.DATE > '11/01/2004'

asks for all data where Table.Date is GREATER than 2004-11-01.

Those two queries are not similar. What values are you really trying to get?

-SQLBill
mrdenny (Programmer)
6 Dec 04 13:28
Also, what data type is the date data saved in within the database?

Denny

--Anything is possible.  All it takes is a little research. (Me)


http://www.mrdenny.com (My very old site)

Eyespi20 (TechnicalUser)
6 Dec 04 13:35
Isn't getdate() a dd/mm/yyyy hh:mm:ss return? so if he's going back 30 days, he'll only get those items that are excactly that day, month, year, hour, minute and second, right?

Isn't dateadd(dd, -30, (cast(convert(char(8),getdate(),1) AS datetime))) going to get him a better match?

Margaret


mrdenny (Programmer)
6 Dec 04 13:46
Sort of.  To get a day match he would need to get the value for 30 days ago, convert to to just the date (the method you give will work fine), and convert the value within the field to the same format.

The reason for that is that when you conver to format 1 you strip off the time, but when it gets compaired against the datetime field in the table, it gets the time of midnight attached.  So it's actually comapiring 11/06/2004 00:00:00.000 (getdate() is acurate to the thousandth of a second, as SQLBill shows above).

Denny

--Anything is possible.  All it takes is a little research. (Me)


http://www.mrdenny.com (My very old site)

dvannoy (MIS) (OP)
6 Dec 04 13:50
I am trying to get all records from today and 30 days ago regardless of what Date I run the query. I was using TABLE.DATE > '11/01/2004' just to make sure the query is returning records.

dvannoy (MIS) (OP)
6 Dec 04 13:55
sorry my date field is set as  datetime

Eyespi20 (TechnicalUser)
6 Dec 04 13:56
I know that when I'm querying my system for the same thing, (all records between today and 30 days ago) I use

select * from tablename where
            date >= dateadd(dd, -30, (cast(convert(char(8),getdate(),1) AS datetime)))

and I get everything.

Margaret
yksvaan (TechnicalUser)
6 Dec 04 14:26
Same result without converting with a format parameter

TABLE.DATE >= DATEADD(dd,-30+DATEDIFF(dd,0,getdate()),0)

Cheers

Backup system is as good as the latest recovery

dvannoy (MIS) (OP)
6 Dec 04 14:31
>= did it...

Thanks

Why do you have to use > ?

ESquared (Programmer)
6 Dec 04 14:34
because as they are saying, all the datetimes in the database with various times throughout the day are NOT equal to 12am exactly, on the same day.

It's just like doing a query for numbers equal to 4 and then wondering why it's not selecting 4.2 and 4.5 and 4.773837483.

You have to do > 4 AND <= 5 to cover the range of 'times' between the two 'dates,' 4 and 5.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

yksvaan (TechnicalUser)
6 Dec 04 14:47
Isn't the assignment to get rows where their dates are equal or greater than 30th date before today?

Or am I missiging something ?

Cheers

Backup system is as good as the latest recovery

yksvaan (TechnicalUser)
6 Dec 04 14:53
Ok, yeah, if you just want that day, you could do like ESquared explained

TABLE.DATE >= DATEADD(dd,-30+DATEDIFF(dd,0,getdate()),0)
AND
TABLE.DATE < DATEADD(dd,-29+DATEDIFF(dd,0,getdate()),0)

or multiple other ways..

Cheers

Backup system is as good as the latest recovery

ESquared (Programmer)
6 Dec 04 15:09
oops

>= 4 AND < 5

I inverted the operators!

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close