GETDATE -30

[dvannoy]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

sorry my date field is set as datetime

 

[Eyespi20]

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]

Same result without converting with a format parameter

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

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[dvannoy]

>= did it...

Thanks

Why do you have to use > ?

 

[ESquared]

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]

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

[blue]Backup system is as good as the latest recovery[/blue]

 

[yksvaan]

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


[blue]Backup system is as good as the latest recovery[/blue]

 

[ESquared]

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.)