using dateadd function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » using dateadd function

using dateadd function

Rate Topic

Display Mode

Topic Options

Author

Message

LeeLuv

Posted Monday, October 15, 2012 2:01 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:31 AM
Points: 25, Visits: 170

Hi All

I'm new @ SQL server. i need to select record that are within 30 days of this given date '20110420'.

Plz help.

Post #1372588

Henrico Bekker

Posted Monday, October 15, 2012 2:10 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 1,871, Visits: 2,692

select criteria from yourtable where datecolumn < dateadd(dd, 30, '20110420')

----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Post #1372589

Eugene Elutin

Posted Monday, October 15, 2012 2:33 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:39 AM
Points: 2,556, Visits: 4,398

Henrico Bekker (10/15/2012)

select criteria from yourtable where datecolumn < dateadd(dd, 30, '20110420')

What do you mean by "within"?
Is it within 30 days before the day?

WHERE datecolumn BETWEEN '20110420' AND DATEADD(dd, -30, '20110420')

Is it within 30 days after the day?

WHERE datecolumn BETWEEN '20110420' AND DATEADD(dd, 30, '20110420')

Is it within 30 days before and 30 days after?

WHERE datecolumn BETWEEN DATEADD(dd, -30, '20110420') AND DATEADD(dd, 30, '20110420')

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1372592

clubbavich

Posted Tuesday, October 16, 2012 3:17 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:56 AM
Points: 56, Visits: 586

I've always avoided using the BETWEEN operator on datetime columns, because most of what I work with should not get any overlap at all between one "window" of time and the next.

So, instead of

--Smaller value MUST come first. Otherwise it returns no results
WHERE datecolumn BETWEEN DATEADD(dd, -30, '20110420') AND '20110420'

Which is the equivalent of

--Includes 2011-04-20 12:00 AM
WHERE datecolumn >=  DATEADD(dd, -30, '20110420') AND datecolumn <= '20110420'

I would use

--Does not include 2011-04-20 12:00 AM 
WHERE datecolumn  >= DATEADD(dd, -30, '20110420') AND datecolumn < '20110420'

This way, I can do a very similar function if I want to get the NEXT set of 30 days, and I will get absolutely no overlaps.

Post #1373547

Siva Ram

Posted Tuesday, October 16, 2012 9:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, December 06, 2012 7:47 AM
Points: 9, Visits: 37

Select columnname1, columnname2
FROM
tablename
WHERE datecolumnname BETWEEN DATEADD(dd, -30, '20110420') AND '20110420'

Post #1373609

LeeLuv

Posted Wednesday, October 17, 2012 12:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:31 AM
Points: 25, Visits: 170

thanx guys my Report is working nicely now.

Post #1373645

« Prev Topic | Next Topic »

Permissions