Prev: Replication Script-Out 2008 Full?
Next: auto - indenting in code editor
From: Chamark via SQLMonster.com on 22 Jan 2010 14:22
Using SQL 2005

I want to be able to take whatever date is provided and report on all records
between that and that date minus 13 months.

Select *
FROM table
WHERE month_dt Between '06/01/2009' and '06/01/2009' -13

Is this possible and if so how? Any help is greatly appreciated.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: Tom Moreau on 22 Jan 2010 14:30
Try:

Select *
FROM table
WHERE month_dt Between dateadd (mm, -13, '06/01/2009') and '06/01/2009'


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Chamark via SQLMonster.com" <u21870(a)uwe> wrote in message
news:a281270390643(a)uwe...
Using SQL 2005

I want to be able to take whatever date is provided and report on all
records
between that and that date minus 13 months.

Select *
FROM table
WHERE month_dt Between '06/01/2009' and '06/01/2009' -13

Is this possible and if so how? Any help is greatly appreciated.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: Chamark via SQLMonster.com on 22 Jan 2010 14:44
Thanks Tom that worked like a charm. You rock!!!

Tom Moreau wrote:
>Try:
>
>Select *
>FROM table
>WHERE month_dt Between dateadd (mm, -13, '06/01/2009') and '06/01/2009'
>
>Using SQL 2005
>
>I want to be able to take whatever date is provided and report on all
>records
>between that and that date minus 13 months.
>
>Select *
>FROM table
>WHERE month_dt Between '06/01/2009' and '06/01/2009' -13
>
>Is this possible and if so how? Any help is greatly appreciated.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1

From: Plamen Ratchev on 22 Jan 2010 16:58
Just note better practice is to use language neutral date format like 'YYYYMMDD:

WHERE month_dt BETWEEN DATEADD(mm, -13, '20090601') AND '20090601'


--
Plamen Ratchev
http://www.SQLStudio.com
From: Uri Dimant on 24 Jan 2010 07:40
Hi
Those two persons with great respect gave solution you are asking for. I
want you just to make sure that if you have store along with dates also
minutes and seconds than the solution won't work I mean you are going to
loose some data like '06/01/2009 15:00'

create table #tmp (dt datetime)

insert into #tmp select getdate()

insert into #tmp select getdate()+1

insert into #tmp select getdate()+2



select * from #tmp

where dt between '20100124' and '20100126'






"Chamark via SQLMonster.com" <u21870(a)uwe> wrote in message
news:a281270390643(a)uwe...
> Using SQL 2005
>
> I want to be able to take whatever date is provided and report on all
> records
> between that and that date minus 13 months.
>
> Select *
> FROM table
> WHERE month_dt Between '06/01/2009' and '06/01/2009' -13
>
> Is this possible and if so how? Any help is greatly appreciated.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201001/1
>


 | 
Pages: 1
Prev: Replication Script-Out 2008 Full?
Next: auto - indenting in code editor