sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-03-09 : 07:30:10

Hi,

I have a datetime column in my table, i want to get the month only for ten months from current month

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-03-09 : 08:29:56

I have a date format like this 2008-02-03 00:00:00.000,

Now i want to get the recent 10 months alone. that means now the current month is Mar 2008, so i want to get 10 months list from curent month

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-09 : 08:35:36

look into dateadd function

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 14:30:52

Select DATEADD(m,-11,getdate())
--2007-04-09 14:30:13.897
Select DATEADD(m,-11,datediff(d,0,getdate()-day(getdate())))
--2007-03-29 00:00:00.000

for example...

Poor planning on your part does not constitute an emergency on my part.

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-03-09 : 20:39:09

you can try this.

declare @i as int
declare @i2 as varchar(20)
set @i = 0

while(@i < 10)
begin
Select @i2 = cast(DATEADD(m,-@i,getdate()) as varchar(12))
set @i2 = left(@i2,4)+' '+right(@i2,5)
print @i2
set @i = @i+1
end

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-09 : 20:57:38

quote:
Originally posted by sign_seventh

you can try this.

declare @i as int
declare @i2 as varchar(20)
set @i = 0

while(@i < 10)
begin
Select @i2 = cast(DATEADD(m,-@i,getdate()) as varchar(12))
set @i2 = left(@i2,4)+' '+right(@i2,5)
print @i2
set @i = @i+1
end

What? You really don't need a while loop for that...



DEclare @today datetime
DECLARE @TenMonthsAgo datetime
Declare @LastdayofLastMonth datetime

SET @today = getdate()
SET @LastdayofLastMonth = @today - day(@today) 
SET @TenMonthsAgo = dateadd(m,-10,@today)


Select DISTINCT START_OF_MONTH_DATE,END_OF_MONTH_DATE
FROM dbo.F_TABLE_DATE('20000701','20080401')
WHERE DATE >= @TenMonthsAgo and DATE <=@LastDayofLastMonth

/*
START_OF_MONTH_DATE	END_OF_MONTH_DATE
2007-05-01 00:00:00.000	2007-05-31 00:00:00.000
2007-06-01 00:00:00.000	2007-06-30 00:00:00.000
2007-07-01 00:00:00.000	2007-07-31 00:00:00.000
2007-08-01 00:00:00.000	2007-08-31 00:00:00.000
2007-09-01 00:00:00.000	2007-09-30 00:00:00.000
2007-10-01 00:00:00.000	2007-10-31 00:00:00.000
2007-11-01 00:00:00.000	2007-11-30 00:00:00.000
2007-12-01 00:00:00.000	2007-12-31 00:00:00.000
2008-01-01 00:00:00.000	2008-01-31 00:00:00.000
2008-02-01 00:00:00.000	2008-02-29 00:00:00.000
*/

NOTE: F_TABLE_DATE is a function available in the script library, and the columns referenced are part of the table valued functions output. The function is not needed for the OP's issue, but used here to represent options for the topic at hand.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources