displaying every month in the table except for 3

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

SQL Server 2005 Forums

Transact-SQL (2005)

displaying every month in the table except for 3

Author

Topic

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-22 : 15:26:24

I have a table wiht a date field , from january to the current date (september) soon to be october. I would like to retrieve the date field displaying only the month and year starting in April. Yes skip january to march data/date.

example

id date
-- -----
2345 09/22/2010 09:33:12
1457 08/12/2010 11:49:33
3489 01/03/2010 22:28:22
2166 04/25/2010 20:44:11
2298 02/02/2010 16:22:03

so the results would be

April
August
September

Thanks all i need this one!

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-22 : 15:30:57

so far i got this to display every month including january februar and march, i want to remove january february and march

DATEADD(yy,-1,GETDATE())
GROUP BY DATEPART(yy,my_Date),DATENAME(mm,my_Date), DATEPART(mm,my_Date)
ORDER BY DATEPART(mm,my_Date)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-22 : 15:42:39

add where date > '20100331' before your group by

Jim

Everyday I learn something that somebody else already knew

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-22 : 16:00:21

can you show me the syntax please

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-22 : 17:39:59

where date > '20100331'
(put this right before your group by clause)

Jim

Everyday I learn something that somebody else already knew

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-22 : 19:57:43

so its , you mean < less than 3/31/2010?

DATEADD(yy,-1,GETDATE()) WHERE my_date < '20100331'
GROUP BY DATEPART(yy,my_Date),DATENAME(mm,my_Date), DATEPART(mm,my_Date)
ORDER BY DATEPART(mm,my_Date)

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-23 : 08:55:45

Hi Jim or anyone

I guess i m not understanding you really.. I have tried to put this statement right before the GROUP BY and it does not work. I tried it with 2 WHERE Clause and that didnt work either. I tried > and < that didnt work. Can someone please help me

I just want to list out the months in order from April, May, June, July, etc.... skipping january february and March. PLEASE HELP!

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-23 : 08:57:09

this is what i have so far, it shows every month that exist in the table or db. as you know i want to remove jan feb and march from showing.

my_date > DATEADD(yy,-1,GETDATE())
GROUP BY DATEPART(yy,my_Date),DATENAME(mm,my_Date), DATEPART(mm,my_Date)
ORDER BY DATEPART(mm,my_Date)

newbietosql1221
Starting Member

25 Posts

Posted - 2010-09-23 : 11:38:40

Sorry how about, in this code that i mention above,

my_date > DATEADD(yy,-1,GETDATE())
GROUP BY DATEPART(yy,my_Date),DATENAME(mm,my_Date), DATEPART(mm,my_Date)
ORDER BY DATEPART(mm,my_Date)

how do you specify the year 2010 because i may need to do the same for 2011, i would like to specify year instead of current year

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-09-23 : 19:40:12

It would be helpful if you would supply the entire SQL statement. Also, can you elaborate on what "doesn't work" means? Error code? Wrong results? Explosions?

=======================================
In all life one should comfort the afflicted, but verily, also, one should afflict the comfortable, and especially when they are comfortably, contentedly, even happily wrong. -John Kenneth Galbraith, economist (1908-2006)

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-24 : 07:51:15

select distinct DATENAME(month,my_Date) from YOUR_TOUR_NAME where DATEPART(mm,my_Date)>3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources